July 7, 2004 at 12:16 pm
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?
July 7, 2004 at 2:27 pm
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
July 8, 2004 at 3:23 am
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.
July 8, 2004 at 4:04 am
Hi,
Here is a similar question:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=119192&p=1
/rockmoose
You must unlearn what You have learnt
July 8, 2004 at 7:28 am
grasshopper and rockmoose....
thanks for your help. I got it working!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply