Select the first instance of a record

sql, sql-server-2008

Solution

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

Problem

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.

Original source