Top 3 Rows by Group

[liscete]

I need to choose top 3 most recnet orders for each individual using order_date.

i have been able to choose top 2 most recent using order_date. But then it becomes complicated down to top 3.

Please use only joins. i'm not familiar with more complicated methods.
Here is my code:

select individual_id, drop_date, response ,
cast(null as int(1)) as score
from #ACTIVITY_1

where drop_date = (select max(drop_date) from #ACTIVITY_1 as f where f.individual_id = #ACTIVITY_1.ind ividual_id)

or drop_date = (select max(drop_date) from #ACTIVITY_1 as f where f.individual_id = #ACTIVITY_1.ind ividual_id
and drop_date < (select max(drop_date) from #ACTIVITY_1 as f2 where f2.individual_i d = #ACTIVITY_1.ind ividual_id) )

Thanks for your help!

[ck9663]

Use code tag, please :)

Try this:

select top 3 individual_id, drop_date, response ,
cast(null as int(1)) as score
from #ACTIVITY_1 
order by drop_date desc

Happy Coding!

-- CK

[dplante]

Dear liscete,

Here is something you might want to look - you need Northwind database and SQL Server 2005.

I would suggest you run the innermost select first, then both the inner and outer select, and you might want to experiment with the 'between 1 and 3' (try between 1 and 10)

Thanks,
Dominique

select * from
(
select orderid, CustomerID, orderdate, row_number() over (partition by customerid order by orderdate desc) as rownumber from orders
) order_view
where order_view.rown umber between 1 and 3