CODEWITHSUNDEEP
sqlsql-serverrank
Rifat Rahman
Rifat Rahman

Reputation: 129

Ranking Sql Server Table Rows When a Column Value Changes

I have a table in SQL Server like this-

ID  Employee_id  group_field_code   from_date              To_date
================================================================================
1   44855           055            2015-04-01 00:00:00  2015-04-02 17:37:26
2   44855           055            2015-04-02 17:37:27  2015-04-25 09:31:10
3   44855           055            2015-04-25 09:31:11  2015-09-18 14:58:59
4   44855           053            2015-09-18 15:00:00  2016-04-02 02:58:59
5   44855           055            2016-04-02 03:00:00  2016-05-26 02:58:59
6   44855           055            2016-05-25 13:25:23  2016-09-07 14:58:59
7   44855           055            2016-05-26 03:00:00  2016-05-25 13:25:22
8   44855           052            2016-09-07 15:00:00  2017-05-22 02:58:59
9   44855           055            2017-05-22 03:00:00  NULL

All I want is New Rank for a Row when the group field changes. Like- For ID 1-3 (group_code 55) I want one Rank, for ID 4 (group_Code changes, 53) I want another rank, then again for ID 5 (group_code 5 re-arrives) I want another rank and so on. Is this possible in SQL Server using Rank or other function?

Upvotes: 5

Views: 2337

Answers (2)

Bharadwaj T
Bharadwaj T

Reputation: 71

`SELECT dt.*,
       sum(dt.rnk) over (
                         ORDER BY dt.id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS final_rnk
FROM
  (SELECT e2.*,
          CASE
          WHEN e1.group_field_code <> e2.group_field_code THEN 1
          ELSE 0
      END AS rnk
   FROM employee e1
   INNER JOIN employee e2 ON e1.id = e2.id-1) dt ;`

result

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

We can try using the difference of row numbers method:

SELECT
    ID, Employee_id, group_field_code, from_date, To_date,
    DENSE_RANK() OVER (ORDER BY rank) rank
FROM
(
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY from_date) -
        ROW_NUMBER() OVER (PARTITION BY group_field_code ORDER BY from_date) AS rank
    FROM yourTable
) t
ORDER BY t.from_date;

Demo here:

Rextester

Upvotes: 3

Related Questions