Menu
I have a query that is currently using a correlated subquery to return the results, but I am thinking the problem could be solved more eloquently perhaps using ROW_NUMBER().
The problem is around the profile of a value v, through a number of years for an Item. Each item has a number of versions, each with its own profile whick starts when the version is introduced 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 most recent 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)
Whilst this returns the correct I suspect there is a more efficient way to do it, especially when the table gets large.
I am using SQL Server 2005.
Thanks in advance
993
A correlated subquery is much slower than a non-correlated subquery because in the former, the inner query executes for each row of the outer query. This means if your table has n rows then whole processing will take the n * n = n^2 time, as compared to 2n times taken by a non-correlated subquery.
Correlated subqueries provide an intuitive syntax for writing queries that return related data. However, they often perform poorly due to needing to execute once for every value they join on . The good news is that many correlated subqueries can be rewritten to use a derived table for improved performance.
When you use a correlated subquery in a DELETE statement, the correlation name represents the row that you want to delete. SQL evaluates the correlated subquery once for each row in the table named in the DELETE statement to decide whether to delete the row. SQL determines, for each row in the CORPDATA.
I would do it with a 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
