Selecting Latest Buys - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting Latest Buys

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-07 : 08:54:51

Joe writes "I have a table that holds the history of purchases and I need to select the latest 2 orders from each customer, the table looks something like this:

Customer ID Item Cost Buy Date Buy Time
1 item1 10 20040708 1234
2 item2 15 20040707 24501
1 item2 12 20040702 35102
3 item3 10 20040702 12541
1 item1 9 20040615 2154

The key for this table is the customer id, buy date and buy time, it is a VERY large data base so efficiency is a big issue as well. Thanks for your help in advance, this web site has been a HUGE help to me so far."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 09:22:24

this should help:


SELECT CustomerID, Item, Cost, BuyDate, BuyTime
From 	MyTable t1
WHERE BuyDate IN (SELECT TOP 2 BuyDate FROM MyTable WHERE CustomerId = t1.CustomerId ORDER BY BuyDate DESC)
ORDER BY CustomerId, BuyDate DESC

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources