Selecting newest entries with different types

Question

I'm designing table which will contain properties of some objects which will change over time.

CREATE TABLE [dbo].[ObjectProperties]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [ObjectType] SMALLINT NOT NULL, 
    [Width] SMALLINT NOT NULL, 
    [Height] SMALLINT NOT NULL, 
    [Weight] SMALLINT NOT NULL
)

Let's say I have this ObjectTypes: 1 = Chair 2 = Table

And Data for this table:

INSERT INTO [dbo].[ObjectProperties] ([Id], [ObjectType], [Width], [Height], [Weight]) VALUES (1, 1, 50, 50, 1000)
INSERT INTO [dbo].[ObjectProperties] ([Id], [ObjectType], [Width], [Height], [Weight]) VALUES (2, 2, 80, 40, 500)
INSERT INTO [dbo].[ObjectProperties] ([Id], [ObjectType], [Width], [Height], [Weight]) VALUES (3, 1, 50, 50, 2000)

So, as you can see I had Chair object which Weight was 1000 then I changed weight to 2000. And I'm storing something like modification history of objects properties. Now I want to select newest data from this table for each object. I know how to select newest data for each object one by one:

SELECT TOP 1 * FROM [ObjectProperties] WHERE ObjectType = 1 ORDER BY Id DESC

But what if I want to select few objects with one query? Like

SELECT ... * FROM [ObjectProperties] WHERE ObjectType IN (1, 2) ...

And receive rows with ids 2 and 3 (because 3 has newer properties for Chair than 1)

Answer 1

You can use a CTE with ROW_NUMBER ranking function:

WITH CTE AS(
    SELECT *, 
       RN=ROW_NUMBER()OVER(PARTITION BY ObjectType ORDER BY ID DESC)
    FROM [ObjectProperties] op
)
SELECT * FROM CTE WHERE RN = 1
AND ObjectType IN (1, 2)

Demo

The ROW_NUMBER returns one row for every ObjectType-group order by ID DESC(so the record with the highest ID) .If you want to filter by certain ID's you just have to apply the appropriate WHERE clause, either in the CTE or in the outer SELECT.

Ranking Functions