Getting the last item in a group by query?
There is likely a blindingly obvious solution to this, I need to do a group by expression in my query and get the latest row based on a date field in the same table. Is this even possible, and any tips on how to do that? Example of data and query: --- Table: =(pseudo table based on origional, trimmed)= 'id', 'bigint(20)', '', 'PRI', '', 'auto_increment' 'date', 'datetime', '', '', '-00-00 00:00:00', '' 'serial_number', 'varchar(25)', '', '', '', '' 'pass', 'varchar(6)', '', '', 'false', '' = SELECT t.pass, t.id FROM theTable t group by t.serial_number --- I have tried putting a sort into the query like so: SELECT t.pass, t.id FROM theTable t GROUP BY t.serial_number ORDER BY t.date desc Which naturally only sorts the resulting rows by date.
Re: Getting the last item in a group by query?
On Tue, Mar 4, 2008 at 10:57 AM, Esbach, Brandon
[EMAIL PROTECTED] wrote:
[snip!]
SELECT
t.pass, t.id
FROM
theTable t
GROUP BY
t.serial_number
ORDER BY
t.date desc
Try adding the LIMIT keyword.
SELECT t.pass, t.id FROM theTable t GROUP BY t.serial_number ORDER
BY t.date DESC LIMIT 0,1;
That will give only the latest date.
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting the last item in a group by query?
On Tue, Mar 4, 2008 at 11:24 AM, Esbach, Brandon
[EMAIL PROTECTED] wrote:
Thanks for the reply,
Sorry, should have been more specific on that :).
I need to access the last record by date for each serial_number in the
table (not just latest record)
Okay, this is untested, so I don't know if it will work
Out-Of-The-Box[tm] or not, but it should at least lead you in the
right direction. Plus, it's back on-list now, so that others can read
the results in the archives when they search on the web. ;-)
SELECT t.pass, t.id FROM theTable t WHERE t.serial_number IN
(SELECT DISTINCT serial_number,date FROM theTable ORDER BY date DESC
LIMIT 0,1);
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting the last item in a group by query?
Hmm didn't notice that replies are sent to personal emails :o!
I'll look down that avenue once I've completed the mysql version upgrade
(mysql 4 on my test bench, mysql5 upgrade in progress on the production
server)
Thanks again!
-Original Message-
From: Daniel Brown [mailto:[EMAIL PROTECTED]
Sent: 04 March 2008 16:56
To: Esbach, Brandon; MySQL User Group
Subject: Re: Getting the last item in a group by query?
On Tue, Mar 4, 2008 at 11:24 AM, Esbach, Brandon
[EMAIL PROTECTED] wrote:
Thanks for the reply,
Sorry, should have been more specific on that :).
I need to access the last record by date for each serial_number in
the table (not just latest record)
Okay, this is untested, so I don't know if it will work
Out-Of-The-Box[tm] or not, but it should at least lead you in the right
direction. Plus, it's back on-list now, so that others can read the
results in the archives when they search on the web. ;-)
SELECT t.pass, t.id FROM theTable t WHERE t.serial_number IN (SELECT
DISTINCT serial_number,date FROM theTable ORDER BY date DESC LIMIT 0,1);
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting the last item in a group by query?
On Tue, Mar 4, 2008 at 12:02 PM, Esbach, Brandon
[EMAIL PROTECTED] wrote:
Hmm didn't notice that replies are sent to personal emails :o!
I'll look down that avenue once I've completed the mysql version upgrade
(mysql 4 on my test bench, mysql5 upgrade in progress on the production
server)
Thanks again!
Yeah, a lot of the lists now are configured so that you have to
hit Reply-All to post back to the list. This way, you can have
off-list discussions by simply clicking Reply.
One question are you building a PHP application with that? If
so, subscribe and send a message over to the PHP-DB list. It's
well-monitored by some very talented people, and you can get more
specific answers. http://www.php.net/mailinglists
If not, feel free to ignore this! ;-P
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting the last item in a group by query?
Hi, On Tue, Mar 4, 2008 at 10:57 AM, Esbach, Brandon [EMAIL PROTECTED] wrote: There is likely a blindingly obvious solution to this, I need to do a group by expression in my query and get the latest row based on a date field in the same table. Is this even possible, and any tips on how to do that? Sure. Two posts that ought to cover your question: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ http://www.xaprb.com/blog/2007/08/21/how-to-select-the-first-or-last-row-per-group-in-sql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
