CODEWITHSUNDEEP
t-sql
dotnet-practitioner
dotnet-practitioner

Reputation: 14148

tsql: need help with returning latest record from a table

If I have data like following: 

CustID, CustDate, CustCode
===========================
123     12/1/10     a
123     12/2/10     b
456     12/3/10     c
456     12/4/10     d
789     12/5/10     3

How would I write the query that would return the latest record for that customer within that table?

Result should be..

CustID, CustDate, CustCode
===========================
123     12/2/10     b
456     12/4/10     d
789     12/5/10     3

Please help me write query..

Upvotes: 0

Views: 60

Answers (2)

Thomas
Thomas

Reputation: 64674

Select CustId, CustDate, CustCode
From Table As T
Where CustDate = (
                    Select Max(T1.CustDate)
                    From Table As T1
                    Where T1.CustID = T.CustID
                    )

Upvotes: 0

Phil Hunt
Phil Hunt

Reputation: 8541

SELECT
  CustId, CustDate, CustCode
FROM
  MyTable
WHERE
  NOT EXISTS
  (
    SELECT * FROM MyTable AS a_MyTable
    WHERE a_MyTable.CustId = MyTable.CustId
      AND a_MyTable.CustDate > MyTable.CustDate
  )

Upvotes: 1

Related Questions