CODEWITHSUNDEEP
sqlsql-server-2008maxdate
Jay
Jay

Reputation: 3082

Selecting the most recent record for a customernumber

I have the following in a table:

Account Name    CustomerNumber  ReadingValue        ReadDate
LISA DODDS      96000293474     35568               2015-05-26 23:00:00.000
TONY DODDS      96001750513     54672               2014-08-26 13:59:05.000
TONY DODDS      96001750513     58624               2015-05-17 23:00:00.000
BRENDA DODDS    96006692968     30945               2015-01-19 14:39:46.000
BRENDA DODDS    96006692968     31168               2015-05-28 23:00:00.000
PAULA DODDS     96006946695     43612               2015-05-28 23:00:00.000
PAULA DODDS     96006946695     56700               2014-07-25 13:55:03.000

How can I select so that it shows only the latest record per customer number?

Upvotes: 0

Views: 40

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460108

You can use a CTE with ROW_NUMBER:

WITH CTE AS
(
    SELECT [Account Name], CustomerNumber, [ReadingValue], ReadDate,
           RN = ROW_NUMBER() OVER (PARTITION BY CustomerNumber ORDER BY ReadDate DESC)
    FROM dbo.TableName
)
SELECT [Account Name], [CustomerNumber], [ReadingValue], ReadDate
FROM CTE
WHERE RN = 1

DEMO

Upvotes: 3

juergen d
juergen d

Reputation: 204766

select t1.*
from your_table t1
join
(
    select customerNumber, max(readDate) as maxdate
    from your_table
    group by customerNumber
) t2 on t1.customerNumber = t2.customerNumber
    and t1.readDate = t2.maxdate

Upvotes: 1

Related Questions