CODEWITHSUNDEEP
sqlsql-server
Darwin Apaza
Darwin Apaza

Reputation: 15

Multiple records not repeated

I have a table called TABLE_SCREW where I want to get the latest records for each code. For example, in the table below you should obtain the records with ids 3 and 7. I am a newbie in sql and I hope you can help me.

TABLE_SCREW

Upvotes: 1

Views: 29

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could use:

SELECT TOP 1 WITH TIES *
FROM TABLE_SCREW
ORDER BY ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY Date DESC);

Another approach(may have better performance):

SELECT *  -- here * should be replaced with actual column names
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY Date DESC) AS rn
      FROM TABLE_SCREW) sub
WHERE sub.rn = 1;

Upvotes: 2

Related Questions