We help IT Professionals succeed at work.

Get Secone to last record per group

H-SC
H-SC asked
on
I have a table that has r1_enter_date and r1_record_type

I would like to get the second to the last  r1_enter_date for each r1_record_type as a group.

example

date                    type
12/15/2011         apple
12/18/2011         apple
12/20/2011         apple
12/10/2011         orange
12/11/2011         orange
12/13/2011         orange


The data that would be brought back for this table would be:

apple          12/18/2011
orange        12/11/2011


Thanks in advance
Comment
Watch Question

HainKurtSr. System Analyst

Commented:
try:

select* from (
select row_number() over (partititon by type order by date desc) rn, t.*
from myTable t
) x where rn=2
Sr. System Analyst
Commented:
here it is
with myTable as (
      select CAST('12/15/2011' as date) date, 'apple' as type
union select CAST('12/18/2011' as date), 'apple'
union select CAST('12/20/2011' as date), 'apple'
union select CAST('12/10/2011' as date), 'orange'
union select CAST('12/11/2011' as date), 'orange'
union select CAST('12/13/2011' as date), 'orange'
)
select * from (
       select row_number() over (partition by type order by date desc) rn, t.*
         from myTable t
) x where rn=2

rn	date	type
2	2011-12-18	apple
2	2011-12-11	orange

Open in new window

Author

Commented:
HainKurt,

This is perfect!  Many thanks for your much needed help.