CODEWITHSUNDEEP
sql-servert-sql
Orkun Berk Yuzbasioglu
Orkun Berk Yuzbasioglu

Reputation: 165

How to rank a column within a group ordered by a date column in SQL Server

I have the following sample data:

car_id  owner_id    service_date
1        user2  2016-01-02 00:00:00
1        user2  2016-03-02 00:00:00
1        user3  2016-11-02 00:00:00
1        user4  2017-01-02 00:00:00
1        user4  2017-02-12 00:00:00
1        user1  2017-03-22 00:00:00
1        user2  2017-03-24 00:00:00

I am trying to rank owner_id column for a given car_id in sql-server.

For a given car_id value, the rank column should increase by one, if the 'owner_id' value is different from the 'owner_id' value in the previous row, where rows are sorted by service_date in ascending order.

Expected Output:

car_id  owner_id    service_date    rnk
1        user2  2016-01-02 00:00:00 1
1        user2  2016-03-02 00:00:00 1
1        user3  2016-11-02 00:00:00 2
1        user4  2017-01-02 00:00:00 3
1        user4  2017-02-12 00:00:00 3
1        user1  2017-03-22 00:00:00 4
1        user2  2017-03-24 00:00:00 5

I tried to find the rank with dense_rank() function by partitioning over the car_id and owner_id. But it lead to wrong ranks.

CREATE TABLE #tb_example (
    car_id INT,
    owner_id NVARCHAR(5),
    service_date SMALLDATETIME
);

INSERT INTO #tb_example (car_id, owner_id, service_date) VALUES (1, 'user2', '2016-01-02');
INSERT INTO #tb_example (car_id, owner_id, service_date) VALUES (1, 'user2', '2016-03-02');
INSERT INTO #tb_example (car_id, owner_id, service_date) VALUES (1, 'user3', '2016-11-02');
INSERT INTO #tb_example (car_id, owner_id, service_date) VALUES (1, 'user4', '2017-01-02');
INSERT INTO #tb_example (car_id, owner_id, service_date) VALUES (1, 'user4', '2017-02-12');
INSERT INTO #tb_example (car_id, owner_id, service_date) VALUES (1, 'user1', '2017-03-22');
INSERT INTO #tb_example (car_id, owner_id, service_date) VALUES (1, 'user2', '2017-03-24');

SELECT *, 
        DENSE_RANK() OVER (PARTITION BY car_id, owner_id ORDER BY service_date) AS rnk 
FROM #tb_example
ORDER BY service_date;

Upvotes: 1

Views: 158

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

;WITH CTE AS
(
    SELECT *,           
            CASE 
              WHEN owner_id <> COALESCE(LAG(owner_id) OVER 
                                  (PARTITION BY car_id ORDER BY service_date), owner_id) 
                     THEN 1
              ELSE 0 
            END AS grp
    FROM #tb_example
)
SELECT *,
       SUM(grp) OVER (PARTITION BY car_id ORDER BY service_date) + 1 AS rnk
FROM cte 
ORDER BY service_date;

Demo here

The query uses LAG to detect changes in owner_id within the same car_id partition, with order defined by servic_date.

Using grp field we use SUM(grp) OVER .. to compute rank as a running total of changes.

Upvotes: 1

Related Questions