CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
SBB
SBB

Reputation: 8970

TSQL Distinct Column

I have a query that is selecting employees from a table.

The table is a historical table so it can contain records for the same person. The one piece of data that is always the same is A.[qid]. The employees name could change so it would insert another record with the changes.

I am trying to select the most recent record for the employee which is why i am ordering by A.[Meta_LogDate]. However, I only want to include 1 record for the employee, not all of them.

When I search, I expect it to give me the most recent record in this table.

I dont think I can do Top because this is an ajax call that is narrowing down the employees as you type so I need to make sure the list still shows all the possible options, just not duplicates. 

SELECT   DISTINCT(A.[qid]),
         A.[FirstName],
         A.[LastName],
         A.[ntid],
         A.[Meta_LogDate]
FROM     EmpTable_historical AS A
WHERE    A.[LastName] + ', ' + A.[FirstName] LIKE '%' + @name + '%'
ORDER BY A.[LastName], A.[Meta_LogDate] DESC
FOR      XML PATH ('emp'), TYPE, ELEMENTS, ROOT ('results');

Upvotes: 2

Views: 133

Answers (1)

Lamak
Lamak

Reputation: 70658

;WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY qid ORDER BY Meta_LogDate DESC)
    FROM EmpTable_historical 
    WHERE [LastName] + ', ' + [FirstName] LIKE '%' + @name + '%'
)
SELECT *
FROM CTE
WHERE RN = 1

Upvotes: 1

Related Questions