 | | | -none- | -none- 2007-10-05 - By Baron Schwartz
Back
Hi Steve,
Steve Kiehl wrote:
> I was a bit stumped on a good method to select the second record for
> each distinct group in a table. Say I have a table like the following:
>
> NAME
> DATE
> AMOUNT
> joe 2007-10-03 19:44:57 45
> joe 2007-10-06 19:46:18 90
> joe 2007-10-07 19:37:21 12
> matt 2007-10-03 19:36:54 23
> matt 2007-10-04 19:37:09 67
> steve 2007-10-03 19:36:35 50
> steve 2007-10-04 19:36:54 12
> steve 2007-10-05 19:37:21 5
>
>
>
> If I want the second date for each name in the table, how would I go
> about doing that? I've found it easy to get the first date for each
> name by a query like this:
>
> SELECT name,MIN(date) FROM table GROUP BY name;
>
> - or -
>
> SELECT name,date FROM table GROUP BY name ORDER BY date;
>
> I still am stumped on how I could get the record pertaining to the
> second date for each name in the table. Your thoughts?
You can use a variation on the techniques here:
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per
-group-in-sql/
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|