simple sql question - 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

SQL Server Development (2000)

simple sql question

Author

Topic

escher
Starting Member

1 Post

Posted - 2005-06-02 : 13:42:34

i've got a transaction table and i want to select the most recent transaction from a list of customers. if there are two or more transactions with the same date, then i don't care which one is chosen. table looks like this:

customerID transactionType transactionDate other other2...
4 sale 12/31/05 5:00pm blah blah2...
5 refund 12/15/05 6:00pm blah blah2...
5 sale 12/15/05 3:00pm blah blah2...
6 sale 12/12/05 1:12pm blah3 blah5...

the customer ID is unique for each customer, but they can have more than one transaction. Although this doesn't work, i'd like to have something like:

SELECT TOP 1 * FROM transactions
GROUP BY customerID
ORDER BY transactionDate

Here's what i'm doing right now: copy all transctions i want into temporry table. remove any transactions that are older than other transactions of the custer. The only thing that remains are two transactions that have the exact same transaction time. details of how i'm doing it so far:

copy from transactions to temp table with a few restraints:

INSERT INTO TempTrans SELECT (type == 'sale' and other='blah')

make a temporary listing of each customer's latest transaction:

INSERT INTO LastTrans (customerID, transDate)
SELECT customerID, MAX(TransactionDate)
FROM TempTrans
GROUP BY customerID

delete all items in temp table that are older than the 'most recent' transaction

DELETE FROM TempTrans
JOIN LastTrans ON TempTrans.customerID = LastTrans.customerID
WHERE TempTrans.transDate < LastTrans.TransDate

this removes every item except for the ones that have the same exact transaction date (which does happen).

so, how can i do this better?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-02 : 13:56:09

You don't need any extra tables. YOu can use a SELECT statement embedded in other SELECT statements in place of table names:


select 
  T.*
from
  Transactions T
inner join
  (select CustomerID, Max(TransDate) as LastTransDate
   from Transactions
   group by customerID) Last
on
  T.CustomerID = Last.CustomerID and
  T.TransDate = Last.LastTransDate

That does the trick.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources