469,117 Members | 1,309 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,117 developers. It's quick & easy.

Selecting Most Recent Record

MSSQL2000

I have a table that contains customer transactions
CustomerID
Transaction
TransactionDate
....

I need to select the most recent record that matches a specific CustomerID.

I am fairly new to SQL, could someone provide a sample select statement.

TIA

Tim Morrison

--
Tim Morrison

--------------------------------------------------------------------------------

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.com
Jul 20 '05 #1
3 43531
SELECT *
FROM Transactions AS T
WHERE transactiondate =
(SELECT MAX(transactiondate)
FROM Transactions
WHERE customerid = T.customerid)

Remember that, unless (customerid, transactiondate) is unique, you may still
get more than one row for a Customerid if there are multiple rows with the
same maximum date.

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #2
TransactionDate contains both a Date and a Time, that being said, there can
NEVER be a record with an EXACT date/time/customer match.

Does MAX take the time part of the field into consideration?

Tim Morrison

"David Portas" <RE****************************@acm.org> wrote in message
news:a5********************@giganews.com...
SELECT *
FROM Transactions AS T
WHERE transactiondate =
(SELECT MAX(transactiondate)
FROM Transactions
WHERE customerid = T.customerid)

Remember that, unless (customerid, transactiondate) is unique, you may still get more than one row for a Customerid if there are multiple rows with the
same maximum date.

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #3
> TransactionDate contains both a Date and a Time, that being said, there
can
NEVER be a record with an EXACT date/time/customer match.
In that case make sure you define a constraint on the table if you haven't
already done so:
UNIQUE (customerid,transactiondate)

Does MAX take the time part of the field into consideration?

Yes
--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Nova's Taylor | last post: by
reply views Thread by Steve | last post: by
2 posts views Thread by Mark Hargreaves | last post: by
1 post views Thread by Ben Gribaudo | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.