CODEWITHSUNDEEP
sql-server
user1647992
user1647992

Reputation: 51

Row_number() Partition by - For Repeated Values

Sorry for the new post. I dont know how specifically search for the my requirement.

Here is the scenario,

          **Location Name      Datetime**
          ABC                  2014-05-01 09:15
          ABC                  2014-05-01 09:25
          XYZ                  2014-05-01 09:35
          PQR                  2014-05-01 09:45
          ABC                  2014-05-01 09:55

These are the data's im having, I need the result as below,

          **Location Name      Datetime**      Row Number
          ABC                  2014-05-01 09:15     1
          ABC                  2014-05-01 09:25     1
          XYZ                  2014-05-01 09:35     2
          PQR                  2014-05-01 09:45     3
          ABC                  2014-05-01 09:55     4

Please help me with this.

Upvotes: 3

Views: 720

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

For SQL Server 2012 or newer, you could for example use LAG() OVER() to compare a row with the previous one. That allows you to count only differences from the last row (here generating 0 or 1 depending on whether there is a difference or not);

If you then use SUM() OVER() to add up the differences, you'll get what you want;

WITH cte AS (
  SELECT [Location Name], [Datetime], 
         CASE WHEN LAG([Location Name]) 
                   OVER (ORDER BY [Datetime]) <> [Location Name] 
         THEN 1 ELSE 0 END rn
  FROM mytable
)
SELECT [Location Name], [Datetime],
       1 + SUM(rn) OVER (ORDER BY [Datetime]) [Row Number]
FROM cte
ORDER BY [Datetime];

An SQLfiddle to test with.

Upvotes: 4

Related Questions