By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,658 Members | 1,908 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,658 IT Pros & Developers. It's quick & easy.

Top 3 Rows by Group

P: 4
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.individual_id)

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

Thanks for your help!
Dec 31 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Use code tag, please :)

Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. select top 3 individual_id, drop_date, response ,
  3. cast(null as int(1)) as score
  4. from #ACTIVITY_1 
  5. order by drop_date desc
  6.  
  7.  
Happy Coding!

-- CK
Dec 31 '08 #2

P: 1
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.rownumber between 1 and 3
Jan 21 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.