Hi Brad!

Another way to think of your problem, is that there should exist one,
but but not two records with a prior datetime. Assuming there are no
duplicate datetimes, you could then do

SELECT M1.ID, M1.MYDATETIME
FROM MYTABLE M1
WHERE EXISTS(
SELECT * FROM MYTABLE M2
WHERE M2.ID = M1.ID
AND M2.MYDATETIME < M1.MYDATETIME)
AND NOT EXISTS(
SELECT * FROM MYTABLE M3
JOIN MYTABLE M4 ON M3.ID = M4.ID
WHERE M3.ID = M1.ID
AND M3.MYDATETIME < M1.MYDATETIME
AND M4.MYDATETIME < M3.MYDATETIME)

HTH,
Set

legacydrum wrote:

> 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.
>
> Sincerely,
>
> Brad