Selecting all columns while using GROUP BY and HAVING

Forum Moderators: open

Message Too Old, No Replies

Selecting all columns while using GROUP BY and HAVING

mysql

noyearzero

6:38 pm on Jan 9, 2009 (gmt 0)

I have a table with a list of subscriptions. Each user can have multiple subscriptions so i want to group by the user and i want to pull the latest entry for that user.

so lets say i do this

SELECT *, MAX(date) FROM subscriptions GROUP BY user

it will return the correct MAX(date), but all the other columns do not correspond to that particular table row. i THOUGHT i should be able to do this...

SELECT *, MAX(date) FROM subscriptions GROUP BY user HAVING date = MAX(date)

i'm not sure why its not returning right....but it seems like its ignoring all entries for users that have more than one subscription.

i was thinking maybe i need to do a subquery, which i've tried but i can't get that either. i'm stumped guys.

camble

4:36 am on Jan 11, 2009 (gmt 0)

If you're only interested in the latest subscription, just use the subselect, as in:

select * from subscriptions s where s.date =
(select max(s2.date) from subscriptions s2 where s2.user = s.user)

noyearzero

2:45 pm on Jan 12, 2009 (gmt 0)

Excellent, this worked. However i'm still curious why the HAVING method didn't work the way i expected. Using subqueries works, but slows down performance.