Top 5 rows for a group

Question

Post reply

Top 5 rows for a group

Carol Adams

SSCrazy

Points: 2320
More actions
July 7, 2004 at 12:16 pm

#108555

I have a table that contains order information. What I want to see is the last 2 orders for all accounts.

My order table has the following columns

order_id, account_id, order_date, order_status, etc.

If I have the following rows.....

1, 10, 5/1/04, C

2, 12, 5/1/04, C

3, 10, 5/10/04, C

4, 19, 5/3/04, C

5, 17, 5/16/04, C

6, 12, 5/29/04, C

7, 10, 6/01/04, C

8, 23, 5/28/04, C

What I want to see

2, 12, 5/1/04, C

3, 10, 5/10/04, C

4, 19, 5/3/04, C

5, 17, 5/16/04, C

6, 12, 5/29/04, C

7, 10, 6/01/04, C

8, 23, 5/28/04, C

Order id #1 is missing in my result, which is what I want because in descending order by account_id and order_date it was the 3rd order and I only want the top 2.

Any ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Sounds like you want the last 2 orders to NOT include the very latest one??

If that is the case you will have to build in a correlated sub-select or some other mechanism that says give me the last 2 orders and make sure it doesnt include the very last one?

Acct 10 has orders for 5/1, 5/10 and 6/1

I hope this is what you are looking for

Good Hunting!

AJ Ahrens

webmaster@kritter.net

AKM SSCommitted Points: 1538 More actions · Answer 2

This should work...

select order_id, account_id, order_date, order_status

from ordertable O

where

-- the latest order for a given account

order_date = (select max(order_date) from ordertable O2 where O.account_id=O2.account_id)

or

-- the not-quite-latest for a given account, ie the latest that is earlier than the very latest

order_date = (select max(order_date) from ordertable O3 where O.account_id=O3.account_id and order_date < (select max(order_date) from ordertable O4 where O3.account_id=O4.account_id))

Note: this will return more than two rows for a given account if there are for example two orders on the same day for the same account.