Selection of the latest recordings of various types

Question

I am developing a table that will contain the properties of some objects that 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 ObjectTypes: 1 = chair 2 = table

And the 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 a Chair object, whose weight was 1000, then I changed the weight to 2000. And I keep something like a history of changes in the properties of objects. Now I want to select new data from this table for each object. I know how to select new data for each object one at a time:

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

But what if I want to select multiple objects with a single query? how

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

And get the rows with identifiers 2 and 3 (because 3 has newer properties for the chair than 1)

Answer 1

You can use CTE with the ranking function ROW_NUMBER :

 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

ROW_NUMBER returns one row for each order of ObjectType -group on the ID DESC (therefore the record with the highest ID). If you want to filter by a specific identifier, you just need to apply the corresponding WHERE , either in the CTE or in an external SELECT .

Ranking functions

Answer 2

A simple (admittedly rough) way is as follows:

 select * from ObjectProperties where id in (select max(id) from ObjectProperties group by objecttype) 

This gives:

 Id ObjectType Width Height Weight ----------- ---------- ------ ------ ------ 2 2 80 40 500 3 1 50 50 2000