Get the first / last row of the nth consecutive group

Question

What is the easiest way to select one record / value from the nth group? The group is determined by the material and price (prices may vary). I need to find the first date of the last and last date of the following last groups of price materials. So I want to know when the price has changed.

I tried the following query to get the first date of the current (last) price, which can return an incorrect date if this price was used before:

DECLARE @material VARCHAR(20) SET @material = '1271-4303' SELECT TOP 1 Claim_Submitted_Date FROM tabdata WHERE Material = @material AND Price = (SELECT TOP 1 Price FROM tabdata t2 WHERE Material = @material ORDER BY Claim_Submitted_Date DESC) ORDER BY Claim_Submitted_Date ASC 

This also returns only the last one, how can I get the previous one? So, the date when the previous price was used last / first?

I simplified my schema and created this sql script with data samples. Here in chronological order. So the line with ID = 7 is what I need, since it has the next price with the last date.

 ID CLAIM_SUBMITTED_DATE MATERIAL PRICE 5 December, 04 2013 12:33:00+0000 1271-4303 20 4 December, 03 2013 12:33:00+0000 1271-4303 20 <-- current 3 November, 17 2013 10:13:00+0000 1271-4846 40 7 November, 08 2013 12:16:00+0000 1271-4303 18 <-- last(desired) 2 October, 17 2013 09:13:00+0000 1271-4303 18 1 September, 17 2013 08:13:00+0000 1271-4303 10 8 September, 16 2013 12:15:00+0000 1271-4303 17 6 June, 23 2013 14:22:00+0000 1271-4303 18 9 January, 11 2013 12:22:10+0000 1271-4303 20 <-- a problem since this is older than the desired but will be returned by my simply sub-query approach above 

Is it possible to parameterize this value, so nthLatestPriceGroup , if I want to know the latest last price date? Note that the query is in a scalar-valued function.

Edit : Thanks a lot to everyone. But, unfortunately, a simple ROW_NUMBER does not seem to help here, since I am trying to get a row with the most recent price up to the current price for this material. Thus, GROUP BY / PARTITION BY material,price contains rows with the same price that are not related to the latest recent group of materials and prices.

Please note that the price may vary from

 Date Price Comment 5 months ago 20 original price, note that this is the same as the curent which causes my query to fail! 3 months ago 18 price has changed, i might need the first and last date 2 months ago 20 price has changed, i might need the first and last date 1 month ago 18 previous price, i need the oldest and newest dates NOW 20 current price, i need the first/oldest date from this group 

So, I need the date of the last line of the last 20 group, the oldest 20 group does not matter. Therefore, I need to somehow group at sequential prices, since the price may repeat after it has already changed.

So in fact I only need the last Claim_Submitted_Date from the price group that starts from 1 month ago ... previous price in the list above, which is the date until the previous price was valid. The other information listed in the comments is simply nice ( nthLatestPriceGroup subtext). This is the line with ID=7 in the above samples. By the way, the oldest row in this price group would be with ID=2 (October 17), and not ID=6 (June 23), even if the latter is older. After that there was a different price (10). This is the reason why I cannot use simple ranking functions.

Answer 1

You will need to use the window function ROWNUMBER in the subquery, ...

something like this will bring you there:

 ROW_NUMBER() OVER(PARTITION BY Price ORDER BY Claim_Submitted_Date DESC) AS Row 

Here the update is based on your fiddle:

 DECLARE @material VARCHAR(20) SET @material = '1271-4303' SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY Material ORDER BY Claim_Submitted_Date ASC) AS rn FROM tabdata t2 WHERE Material = @material ) res WHERE rn=2 

If idData is incremental (and therefore chronological), you can use this:

 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY Material ORDER BY idData DESC) AS rn FROM tabdata t2 WHERE Material = @material ) res 

Looking at your latest requirements, we can all think about it (if I understand you correctly):

 DECLARE @MATERIAL AS VARCHAR(9) SET @MATERIAL = '1271-4303' SELECT TOP 1 * FROM tabdata t2 WHERE Material = @material AND PRICE <> ( SELECT TOP 1 Price FROM tabdata WHERE Material = @material ORDER BY CLAIM_SUBMITTED_DATE desc) ORDER BY CLAIM_SUBMITTED_DATE desc --results idData Claim_Submitted_Date Material Price 7 2013-11-08 12:16:00.000 1271-4303 18 

Here's a fiddle based on this.

Answer 2

After your last comments, the only solution I came up with is to count the different price groups according to their Claim_Submitted_Date , and then include the resulting group indices as part of the grouping criteria. Not sure if this will be very effective. Hope this helps.

 declare @materialId nvarchar(max), @targetrank int set @materialId = '1271-4303' set @targetrank =2 ;with grouped as ( select *, (select count( t.price) -- don't put a DISTINCT here. (I know, I did) from tabdata as t where t.Price <> tj.Price and t.Claim_Submitted_Date> tj.Claim_Submitted_Date and t.Material= @materialId )as group_indicator from tabdata tj where Material= @materialId ), rankedClaims as ( select grouped.*, row_number() over (PARTITION BY material,price,group_indicator ORDER BY claim_submitted_date desc) as rank from grouped ), numbered as ( select *, ROW_NUMBER() OVER (order by Claim_Submitted_Date desc) as RowNumber from rankedClaims where rank =1 ) select Id, Claim_Submitted_Date, Material, Price from numbered where RowNumber=@targetrank 

(I'm also not sure that two claims at different prices on the same date should be considered t.Claim_Submitted_Date> tj.Claim_Submitted_Date )

-------------------- Previous answer

Perhaps you can try something like:

 SELECT ranked.[CLAIM_SUBMITTED_DATE] FROM ( SELECT trimmed.*, ROW_NUMBER() OVER (ORDER BY claim_submitted_date) AS rank FROM ( SELECT a.* ,row_number() over (PARTITION BY material,price ORDER BY claim_submitted_date) AS daterank FROM tabdata a WHERE a.material= '1271-4303' ) AS trimmed WHERE daterank=1 ) AS ranked WHERE rank=2 

Parameterization of the rank seems possible, since it only participates in WHERE rank=2

Answer 3

try it

 DECLARE @material VARCHAR(20), @Nth INT SET @material = '1271-4303' SET @Nth = 2 ;with CTE1 ([idData],[Claim_Submitted_Date], [Material], [Price], Rn) as ( SELECT *, DENSE_RANK() OVER(ORDER BY PRICE DESC) AS rn FROM tabdata WHERE Material = @material ) , CTE2 ([idData], [Material], [Price], LastDate) AS( SELECT [idData], [Material], [Price], MAX([Claim_Submitted_Date]) FROM CTE1 WHERE rn = @Nth GROUP BY [idData], [Material], [Price] ) SELECT Top 1 [idData], [Material], [Price], LastDate FROM CTE2 ORDER BY LastDate DESC 

Result set

 idData Material Price LastDate 7 1271-4303 18 2013-11-08 12:16:00.000 

Answer 4

Have you tried window functions like row_number ()

  select a.[IDDATA] , a.[CLAIM_SUBMITTED_DATE] , a.[MATERIAL] , a.[PRICE] ,row_number() over (PARTITION by material,price order by claim_submitted_date) as seq from tabdata a where a.material= '1271-4303' 

SQLFiddle