Calculate group functions (count, min, max ...) for a column value without grouping

Question

Let's say I have the following table:

Table users (id, name, age, city)

1 John    26 New York
2 Aaron   31 Dallas
3 Jenifer 35 Dallas
4 Connely 34 New York

And I would like to have a table with all users with their city's minimum user age:

1 John    26 New York 26
2 Aaron   31 Dallas   31
3 Jenifer 35 Dallas   31
4 Connely 34 New York 26

This example is a bit artificial, but I haven't found a better example.

If I use a query like:

SELECT * FROM users MIN (age) GROUP BY city

I will probably get:

1 John    26 New York
2 Aaron   31 Dallas

I'm not sure if it will return lines 1 and 2, but that's not the point: as a result, I need to have all users.

Is it possible?

+3

Vlada Katlinskaya 05 dec. At 14:53

2 answers

And I would make a sub-selection:

select u.*, (select MIN(age) from users s where s.city = u.city)
from users u;

+1

jarlh 05 dec. 14 at 15:08

Gordon linoff · Accepted Answer · 2014-12-05T14:55:37+0000

I would tend to do this with join

:

select u.*, c.minage
from users u join
     (select city, min(age) as minage
      from users
      group by city
     ) c
     on u.city = c.city;