f



retrieve most recent record for each user

I have this query that returns all of the status records for every
user (multiple entries per user):

SELECT user_id, status, status_date FROM status_table ORDER BY
application_date DESC


How can I get it to return just the most recent "status_date" for all
users?  I tried group by but the sort happens after then grouping so
this won't work:

SELECT count(id) AS num_recs, user_id, status, status_date FROM
status_table GROUP BY user_id ORDER BY application_date DESC

Currently I am just retrieving all 18k status records then dumping
them into an array to dedupe in php (cuts it down to 6k records),  I
would rather only retrieve 6k records and not have to loop through
them.

The server I am on does not support sub queries.
0
3/25/2010 5:25:52 PM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

10 Replies
4481 Views

Similar Articles

[PageSpeed] 1

On Mar 25, 7:25=A0pm, Eric Wilson <ewilson...@gmail.com> wrote:
> I have this query that returns all of the status records for every
> user (multiple entries per user):
>
> SELECT user_id, status, status_date FROM status_table ORDER BY
> application_date DESC
>
> How can I get it to return just the most recent "status_date" for all
> users? =A0I tried group by but the sort happens after then grouping so
> this won't work:
>
> SELECT count(id) AS num_recs, user_id, status, status_date FROM
> status_table GROUP BY user_id ORDER BY application_date DESC
>
> Currently I am just retrieving all 18k status records then dumping
> them into an array to dedupe in php (cuts it down to 6k records), =A0I
> would rather only retrieve 6k records and not have to loop through
> them.
>
> The server I am on does not support sub queries.

The MAX function does what you want.  For example:

SELECT user_id, MAX(status_date) FROM status_table GROUP BY user_id;

would give you the most recent status_date per user_id.

For more info, see:

http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html
http://dev.mysql.com/doc/refman/5.1/en/group-by-optimization.html
0
Nilone
3/25/2010 6:48:06 PM
On Mar 25, 1:48=A0pm, Nilone <rea...@gmail.com> wrote:
> On Mar 25, 7:25=A0pm, Eric Wilson <ewilson...@gmail.com> wrote:
>
>
>
> > I have this query that returns all of the status records for every
> > user (multiple entries per user):
>
> > SELECT user_id, status, status_date FROM status_table ORDER BY
> > application_date DESC
>
> > How can I get it to return just the most recent "status_date" for all
> > users? =A0I tried group by but the sort happens after then grouping so
> > this won't work:
>
> > SELECT count(id) AS num_recs, user_id, status, status_date FROM
> > status_table GROUP BY user_id ORDER BY application_date DESC
>
> > Currently I am just retrieving all 18k status records then dumping
> > them into an array to dedupe in php (cuts it down to 6k records), =A0I
> > would rather only retrieve 6k records and not have to loop through
> > them.
>
> > The server I am on does not support sub queries.
>
> The MAX function does what you want. =A0For example:
>
> SELECT user_id, MAX(status_date) FROM status_table GROUP BY user_id;
>
> would give you the most recent status_date per user_id.
>
> For more info, see:
>
> http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.htmlhttp://dev.=
mysql.com/doc/refman/5.1/en/group-by-optimization.html

Actually, I also need the status that is associated with the most
recent status date...
0
Eric
3/25/2010 7:10:47 PM
On Mar 25, 1:48=A0pm, Nilone <rea...@gmail.com> wrote:
> On Mar 25, 7:25=A0pm, Eric Wilson <ewilson...@gmail.com> wrote:
>
>
>
> > I have this query that returns all of the status records for every
> > user (multiple entries per user):
>
> > SELECT user_id, status, status_date FROM status_table ORDER BY
> > application_date DESC
>
> > How can I get it to return just the most recent "status_date" for all
> > users? =A0I tried group by but the sort happens after then grouping so
> > this won't work:
>
> > SELECT count(id) AS num_recs, user_id, status, status_date FROM
> > status_table GROUP BY user_id ORDER BY application_date DESC
>
> > Currently I am just retrieving all 18k status records then dumping
> > them into an array to dedupe in php (cuts it down to 6k records), =A0I
> > would rather only retrieve 6k records and not have to loop through
> > them.
>
> > The server I am on does not support sub queries.
>
> The MAX function does what you want. =A0For example:
>
> SELECT user_id, MAX(status_date) FROM status_table GROUP BY user_id;
>
> would give you the most recent status_date per user_id.
>
> For more info, see:
>
> http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.htmlhttp://dev.=
mysql.com/doc/refman/5.1/en/group-by-optimization.html

Thanks! This led me to:
SELECT count(user) AS num_recs, user, MAX(CONCAT(status_date, '&',
status)) AS most_recent FROM status_table GROUP BY user
then I explode the most_recent field to get the 2 values I am after.
0
Eric
3/25/2010 7:27:57 PM
On Mar 25, 9:27=A0pm, Eric Wilson <ewilson...@gmail.com> wrote:
> Thanks! This led me to:
> SELECT count(user) AS num_recs, user, MAX(CONCAT(status_date, '&',
> status)) AS most_recent FROM status_table GROUP BY user
> then I explode the most_recent field to get the 2 values I am after.

Interesting solution.  I suspected that you wanted the status too, but
couldn't see how to do it in a simple query.  If anyone has a
different solution, I'd be interested to see it.

One caveat, I think your technique prevents the use of an index to
optimize the query.
0
Nilone
3/26/2010 7:49:38 AM
On 26 Mar, 07:49, Nilone <rea...@gmail.com> wrote:
> On Mar 25, 9:27=A0pm, Eric Wilson <ewilson...@gmail.com> wrote:
>
> > Thanks! This led me to:
> > SELECT count(user) AS num_recs, user, MAX(CONCAT(status_date, '&',
> > status)) AS most_recent FROM status_table GROUP BY user
> > then I explode the most_recent field to get the 2 values I am after.
>
> Interesting solution. =A0I suspected that you wanted the status too, but
> couldn't see how to do it in a simple query. =A0If anyone has a
> different solution, I'd be interested to see it.
>
> One caveat, I think your technique prevents the use of an index to
> optimize the query.

The answer to how to do it is the Strawberry query:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.htm=
l
0
Captain
3/26/2010 9:36:28 AM
On Mar 26, 4:36=A0am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 26 Mar, 07:49, Nilone <rea...@gmail.com> wrote:
>
> > On Mar 25, 9:27=A0pm, Eric Wilson <ewilson...@gmail.com> wrote:
>
> > > Thanks! This led me to:
> > > SELECT count(user) AS num_recs, user, MAX(CONCAT(status_date, '&',
> > > status)) AS most_recent FROM status_table GROUP BY user
> > > then I explode the most_recent field to get the 2 values I am after.
>
> > Interesting solution. =A0I suspected that you wanted the status too, bu=
t
> > couldn't see how to do it in a simple query. =A0If anyone has a
> > different solution, I'd be interested to see it.
>
> > One caveat, I think your technique prevents the use of an index to
> > optimize the query.
>
> The answer to how to do it is the Strawberry query:http://dev.mysql.com/d=
oc/refman/5.0/en/example-maximum-column-group-r...

No, that would be the answer to how to do this if my server wasn't
complete crap.  The very last line of my original post very clearly
stated that the server I am having to use does not support sub
queries.

In my particular situation the answer I ended up using was to just
leave it alone and let php handle it.  I tried the max(concat()) query
and while it did run twice as fast it also was just too nasty to look
at to contemplate using it.  I posted a simplified example here, the
reality was that I needed 6 fields back not just status.

An extra 1/3 of a second on a page that takes over 5 seconds to render
isn't worth the maintenance nightmare.  At least I learned something I
just hope I never have to use it (which is probably an even more
valuable lesson).
0
Eric
3/26/2010 1:09:08 PM
On Mar 26, 3:09=A0pm, Eric Wilson <ewilson...@gmail.com> wrote:
> On Mar 26, 4:36=A0am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 26 Mar, 07:49, Nilone <rea...@gmail.com> wrote:
>
> > > On Mar 25, 9:27=A0pm, Eric Wilson <ewilson...@gmail.com> wrote:
>
> > > > Thanks! This led me to:
> > > > SELECT count(user) AS num_recs, user, MAX(CONCAT(status_date, '&',
> > > > status)) AS most_recent FROM status_table GROUP BY user
> > > > then I explode the most_recent field to get the 2 values I am after=
..
>
> > > Interesting solution. =A0I suspected that you wanted the status too, =
but
> > > couldn't see how to do it in a simple query. =A0If anyone has a
> > > different solution, I'd be interested to see it.
>
> > > One caveat, I think your technique prevents the use of an index to
> > > optimize the query.
>
> > The answer to how to do it is the Strawberry query:http://dev.mysql.com=
/doc/refman/5.0/en/example-maximum-column-group-r...
>
> No, that would be the answer to how to do this if my server wasn't
> complete crap. =A0The very last line of my original post very clearly
> stated that the server I am having to use does not support sub
> queries.
>
> In my particular situation the answer I ended up using was to just
> leave it alone and let php handle it. =A0I tried the max(concat()) query
> and while it did run twice as fast it also was just too nasty to look
> at to contemplate using it. =A0I posted a simplified example here, the
> reality was that I needed 6 fields back not just status.
>
> An extra 1/3 of a second on a page that takes over 5 seconds to render
> isn't worth the maintenance nightmare. =A0At least I learned something I
> just hope I never have to use it (which is probably an even more
> valuable lesson).

Does your server support LEFT JOIN?  If so, you could use the third
solution on that page.  If not, then you have my sympathy! ;)
0
Nilone
3/26/2010 1:35:21 PM
On 26 Mar, 13:09, Eric Wilson <ewilson...@gmail.com> wrote:
> On Mar 26, 4:36=A0am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 26 Mar, 07:49, Nilone <rea...@gmail.com> wrote:
>
> > > On Mar 25, 9:27=A0pm, Eric Wilson <ewilson...@gmail.com> wrote:
>
> > > > Thanks! This led me to:
> > > > SELECT count(user) AS num_recs, user, MAX(CONCAT(status_date, '&',
> > > > status)) AS most_recent FROM status_table GROUP BY user
> > > > then I explode the most_recent field to get the 2 values I am after=
..
>
> > > Interesting solution. =A0I suspected that you wanted the status too, =
but
> > > couldn't see how to do it in a simple query. =A0If anyone has a
> > > different solution, I'd be interested to see it.
>
> > > One caveat, I think your technique prevents the use of an index to
> > > optimize the query.
>
> > The answer to how to do it is the Strawberry query:http://dev.mysql.com=
/doc/refman/5.0/en/example-maximum-column-group-r...
>
> No, that would be the answer to how to do this if my server wasn't
> complete crap. =A0The very last line of my original post very clearly
> stated that the server I am having to use does not support sub
> queries.

Try reading the page to which I referred you!
It says:
"The preceding example uses a correlated subquery, which can be
inefficient (see Section 12.2.9.7, =93Correlated Subqueries=94). Other
possibilities for solving the problem are to use an uncorrelated
subquery in the FROM clause or a LEFT JOIN:"

Note that it tells you how to do it WITHOUT USING SUB-QUERIES OF ANY
SORT.
0
Captain
3/26/2010 1:58:57 PM
On Mar 26, 8:58=A0am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 26 Mar, 13:09, Eric Wilson <ewilson...@gmail.com> wrote:
>
>
>
> > On Mar 26, 4:36=A0am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 26 Mar, 07:49, Nilone <rea...@gmail.com> wrote:
>
> > > > On Mar 25, 9:27=A0pm, Eric Wilson <ewilson...@gmail.com> wrote:
>
> > > > > Thanks! This led me to:
> > > > > SELECT count(user) AS num_recs, user, MAX(CONCAT(status_date, '&'=
,
> > > > > status)) AS most_recent FROM status_table GROUP BY user
> > > > > then I explode the most_recent field to get the 2 values I am aft=
er.
>
> > > > Interesting solution. =A0I suspected that you wanted the status too=
, but
> > > > couldn't see how to do it in a simple query. =A0If anyone has a
> > > > different solution, I'd be interested to see it.
>
> > > > One caveat, I think your technique prevents the use of an index to
> > > > optimize the query.
>
> > > The answer to how to do it is the Strawberry query:http://dev.mysql.c=
om/doc/refman/5.0/en/example-maximum-column-group-r...
>
> > No, that would be the answer to how to do this if my server wasn't
> > complete crap. =A0The very last line of my original post very clearly
> > stated that the server I am having to use does not support sub
> > queries.
>
> Try reading the page to which I referred you!
> It says:
> "The preceding example uses a correlated subquery, which can be
> inefficient (see Section 12.2.9.7, =93Correlated Subqueries=94). Other
> possibilities for solving the problem are to use an uncorrelated
> subquery in the FROM clause or a LEFT JOIN:"
>
> Note that it tells you how to do it WITHOUT USING SUB-QUERIES OF ANY
> SORT.

Oops sorry about that, performance-wise this was the slowest method.
I may still use it though as I can avoid additional array manipulation
in php.
0
Eric
3/26/2010 4:47:00 PM
On Mar 26, 8:35=A0am, Nilone <rea...@gmail.com> wrote:
> Does your server support LEFT JOIN? =A0If so, you could use the third
> solution on that page. =A0If not, then you have my sympathy! ;)

It supports left join the problem was how to convert a 1 to many
result to be 1 to 1 with the matching record being the most recent.

Ended up using the dev.mysql solution that Captain Paralytic posted:

SELECT u.id AS uid, u.first_name, u.last_name, v1.status, v1.valid,
v1.verified_by, v1.verification_date, v1.application_date
FROM verification_table AS v1
LEFT JOIN verification_table AS v2 ON v1.user_id=3Dv2.user_id AND
v1.verification_date < v2.verification_date
LEFT JOIN user_table AS u ON u.id=3Dv1.user_id
WHERE v2.verification_date IS NULL....

This is faster than what I was doing (especially for small sets) and
is a fair bit easier to maintain.
0
Eric
3/26/2010 5:44:38 PM
Reply: