Select top n rows for each value 1

[onwagena]

I have a table looking like this:

Verr Date
1 1-9-2006
1 2-9-2006
1 3-9-2006
2 5-9-2006
2 6-9-2006
3 2-9-2006
4 1-9-2006
4 5-9-2006
4 7-9-2006
4 9-9-2006

I would like to see the latest 2 dates of every Verr. The result must look like

Verr Date
1 2-9-2006
1 3-9-2006
2 5-9-2006
2 6-9-2006
3 2-9-2006
4 7-9-2006
4 9-9-2006

Who knows a solution to this problem?

 

[taupirho]

What version of Oracle are you on?

 

[onwagena]

Oracle 9i

 

[taupirho]

Try this

select verr,date from
(
select verr,date,dense_rank() over(partition by verr order by verr,date desc) rnk
from your_table) t1
where t1.rnk <=2

 

[onwagena]

Thanks,

This is wat I was looking for.