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
>
>

you could use an SP, you could use skip/first, etc.

one option would be

select
Y1.ID, Y1.datefield
from
YourTable Y1
where
(select
count(*)
from
YourTable Y2
where
Y2.ID = Y1.ID and
Y2.DateField > Y1.DateField) = 1

I think it will be slow, but git it a try.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br