Re: GROUP BY & column not in select list

On Mar 3, 5:31 pm, Lew <l...@[EMAIL PROTECTED]
> wrote:
> Lew wrote:
> > Lew wrote:
> >>  SELECT name, MAX(userid) AS userid, MAX(age)
> >>   FROM users GROUP BY name
> >>    ORDER BY name;
>
> > Crap.  This won't work.
>
> MAX() works independently on each column, so this "solution" will not
> guarantee correct correlation of userid to age.
>
> "Correlation", did I say?  This calls for a correlated subquery:
>
>   SELECT name, userid, age
>    FROM users u WHERE age IN
>     (SELECT MAX(age) FROM users x WHERE x.name = u.name)
>   ;
>
> --
> Lew

Thanks Lew,

I make some test runs:

test_db=# select * from users;
 userid | uname | uage
--------+-------+------
      1 | Sami  |    5
      7 | Sami  |   76
      2 | Seppo |   12
      3 | Seppo |   22
      4 | Seppo |   15
      5 | Timo  |   18
      6 | Jukka |   45
(7 rows)

test_db=# SELECT uname, userid, uage
test_db-#    FROM users u WHERE uage IN
test_db-#     (SELECT MAX(uage) FROM users x WHERE x.uname = u.uname)
test_db-#   ;
 uname | userid | uage
-------+--------+------
 Sami  |      7 |   76
 Seppo |      3 |   22
 Timo  |      5 |   18
 Jukka |      6 |   45
(4 rows)

And in this simple example with subquery works fine. Actually in my
real case
I was already trying this kind of statement but I think that there
was
something else wrong. I will find out what it was..

Once again, thanks!

 anssi