> I am in need of some SQL help. I have a detailed table with multiple
> records for each ID with date/times as listed below.
>
> ID DATETIME
> 2 1/14/2007 9:18:26 AM
> 2 1/14/2007 9:20:10 AM
> 2 1/14/2007 9:21:17 AM
> 2 1/14/2007 9:23:05 AM
> 3 1/20/2007 12:07:15 AM
> 3 1/20/2007 12:09:19 AM
> 3 1/20/2007 12:15:45 AM
> 3 1/20/2007 12:22:50 AM
>
> I need to be able to query the data and return the second greatest
> time for each ID which would result as follows.
>
> ID DATETIME
> 2 1/14/2007 9:20:10 AM
> 3 1/20/2007 12:09:19 AM
>
> If it were the first or last record, I could use SELECT MAX.
> However, I am not sure how to do this. Any idea's would be greatly
> appreciated.

if there IS a second greatest value, this could work:
select d1.id, min(d1.val)
from DT d1
where val > (select min(d2.val) from dt d2 where d2.id = d1.id)
group by d1.id

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com