Retrieve the first occurrence of a record by ID in SQL

I am trying to import some data into Excel from a SQL server, but I am having some issues. It’s my first time writing a query, although I am able to get the entire dataset, I have realized that the data is full of duplicates. I want to get rid of duplicates in Excel and only keep Unique records. I can’t use “Remove Duplicates” in Excel, because the data is set to refresh every 30 minutes, and I would hate to remove them as frequently.

My data is set up as:

ID-Code-Status-Status

1-  A-  In-   Employee
2-  A-  In-   Employee
3-  B-  Out-   Temp
4-  A-  Out-   Employee
5-  A-  Out-   Employee

Required

ID-Code-Status

1-  A-  In-   Employee
3-  B-  Out-   Temp
4-  A-  Out-   Employee

I have tried using distinct, but it got me more confused.

Thank you very much.

Answer

You could use a subquery that selects the first occurrance of each code and status (e.g. A, In):

SELECT * FROM [TableName] where [ID] IN
  (SELECT MIN([ID]) FROM [TableName] GROUP BY [Code], [Status])

Advertisement

Answer