Selecting Most Recent Record

[Tim Morrison]

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

[David Portas]

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
--

[Tim Morrison]

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
--

[David Portas]

> 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
--