Selecting the most recent record for a customernumber

Question

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?

Answer 1

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

Answer 2

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