0

I have got the set of data as follow

temperature
-----------
10
20
30
20
30
20
10

I'm trying to get this result

temperature      row_num
-------------------------
10                  1
20                  2
30                  3
20                  1
30                  2
20                  1
10                  1

Reset row_number when the temperature is less than the previous value.

Please help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Milan
  • 9
  • 2
  • Welcome to Stack Overflow. What have you tried so far? Please read the [question guide](https://stackoverflow.com/help/how-to-ask) and see [what is expected](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users). – Stu Apr 08 '22 at 21:46
  • what do you mean by Reset row_number ? – Omar Tammam Apr 08 '22 at 21:50
  • 1
    Is there any sort of timestamp associated with the temperature records? Are there any other columns other than temperature you have to work with? – Austin557 Apr 08 '22 at 21:52
  • Does this answer your question? [Resetting Row number according to record data change](https://stackoverflow.com/questions/27680999/resetting-row-number-according-to-record-data-change) – Stu Apr 08 '22 at 21:59
  • No there is only one column – Milan Apr 10 '22 at 14:45
  • What you are asking then is not possible on that data set, tables do not have any inherent ordering, you would need to add a column that defines the "order of temperatures" - eg a date in order for the boudaries of groups to be identified. – Stu Apr 10 '22 at 16:13

1 Answers1

0

As a table is an unordered set, your question does not make sense without an ordering column. (eg TemperatureTime or an identity column.) ie A table is NOT a list.

Due to the way SQL Server is structured internally ordering by (SELECT NULL) will probably order by the order of insertion for a small table under low load. This is NOT guaranteed!

Something like:

WITH Boundaries
AS
(
    SELECT temperature
        ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
        ,CASE
            WHEN temperature < COALESCE(LAG(temperature) OVER (ORDER BY (SELECT NULL)), -1000)
            THEN 1
            ELSE 0
        END AS Boundary
    FROM YourTable
)
,Grps
AS
(
    SELECT temperature, rn
        ,SUM(Boundary) OVER (ORDER BY rn) AS Grp
    FROM Boundaries
)
SELECT temperature
    ,ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY temperature) AS row_num
FROM Grps
ORDER BY rn
OPTION (MAXDOP 1);
Aardvark
  • 174
  • 1
  • 7