T SQL - eloquent replacement for correlated subquery

Question

I have a query that currently uses a correlated subquery to return results, but I think the problem can be solved more eloquently, possibly using ROW_NUMBER ().

The problem is related to the profile of the value of v after several years for the element. Each element has several versions, each of which has its own profile, which begins when a version is entered, and the data currently looks like this:

    
 ItemId ItemVersionId Year Value
 =============================================
 1 1 01 0.1
 1 1 02 0.1
 1 1 03 0.2
 1 1 04 0.2
 1 1 05 0.2
 1 1 06 0.3
 1 1 07 0.3
 1 1 08 0.4
 1 2 04 0.3
 1 2 05 0.3
 1 2 06 0.3
 1 2 07 0.4
 1 2 08 0.5
 1 3 07 0.6
 1 3 08 0.7
 2 1 01 0.1
 2 1 01 0.1
 2 1 01 0.2
 etc

I want to return the full profile for an item using the latest version, where applicable. For the above example for item 1:

  ItemId ItemVersionId Year Value
 =============================================
 1 1 01 0.1
 1 1 02 0.1
 1 1 03 0.2
 1 2 04 0.3
 1 2 05 0.3
 1 2 06 0.3
 1 3 07 0.6
 1 3 08 0.7

I am currently using

SELECT ItemId, ItemVersionId, Year, Value FROM table t WHERE ItemId = 1 AND ItemVersionId = (SELECT MAX(ItemVersionId) FROM table WHERE ItemId = t.ItemId AND Year = t.Year) 

While this returns the correct one, I suspect there is a more efficient way to do this, especially when the table becomes large.

I am using SQL Server 2005.

Thanks in advance

Answer 1

I would do it with CTE:

 WITH Result AS ( SELECT Row_Number() OVER (PARTITION BY ItemId, Year ORDER BY ItemversionId DESC) AS RowNumber ,ItemId ,ItemversionId ,Year ,Value FROM table ) SELECT ItemId ,ItemversionId ,Year ,Value FROM Result WHERE RowNumber = 1 ORDER BY ItemId, Year 

Answer 2

I think it's alright how you do it. You can check if there is a composite index for ItemId and Year .

You can check the query plan to see the impact of this request.

If your database has an “Item” table, you can try a different approach. Insert the ItemVersionId column into this table and make sure that you update this value while saving new versions. Then in your query, attach the Item table using ItemId and ItemVersionId , instead of using this subquery.

Answer 3

It works?

SELECT ItemId, ItemVersionId, Year, MAX (Value) FROM table GROUP BY ItemId, ItemVersionId, Year

Answer 4

This should work, although you will have to check the performance with your own data:

 SELECT T1.ItemID, T1.ItemVersionID, T1.Year, T1.Value FROM MyTable T1 INNER JOIN (SELECT Year, MAX(ItemVersionID) AS MaxItemVersionID FROM MyTable T2 WHERE T2.ItemID = 1 GROUP BY Year) SQ ON SQ.Year = T1.Year AND SQ.MaxItemVersionID = T1.ItemVersionID WHERE T1.ItemID = 1 

In addition, you can modify the subquery to also group and return the ItemID so that you can return data for more than one item at a time if you need to for any other part of your application. Just be sure to add the ItemID to your join criteria.