Select the first instance of a record

I have a table, myTable that has two fields in it ID and patientID. The same patientID can be in the table more than once with a different ID. How can I make sure that I get only ONE instance of every patientID.?

EDIT: I know this isn’t perfect design, but I need to get some info out of the database and today and then fix it later.

Answer

You could use a CTE with ROW_NUMBER function:

WITH CTE AS(
    SELECT myTable.*
    , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
    FROM myTable 
)
SELECT * FROM CTE
WHERE RN = 1

​x
 
WITH CTE AS(    SELECT myTable.*    , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)    FROM myTable )SELECT * FROM CTEWHERE RN = 1​

Advertisement

Answer