Get first/last row of n-th consecutive group

Question

What's the easiest way to select a single record/value from the n-th group? The group is determined by a material and it's price(prices can change). I need to find the first date of the last and the last date of the next to last material-price-groups. So i want to know when exactly a price changed. I've tried following query to get the first date of the current(last) price which can return the wrong date if that 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 only returns the last, how do i get the previous? So the date when the previous price was used last/first? I have simplified my schema and created this sql-fiddle with sample-data. Here in chronological order. So the row with ID=7 is what i need since it's has the…

Accepted Answer

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

something like this will get you there:

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

Here's the update 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 could 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 could all be over thinking 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.

Get first/last row of n-th consecutive group

Solution

Problem