How to rank rows based on two columns using SQLite

Question

I have a table like this. I want to rank each row based on the date column so that the end result is the same rank if the two values of record_number and date are equal and different rank if the record_number and date is different. For example, the first two rows in the following table should have rank 1, while the third row should take rank 2 since the date is different but the record_number is the same.

record number	date
4648	2015-06-17
4648	2015-06-17
4648	2015-06-18
4897	2015-12-03
6523	2020-01-09
6523	2022-04-01

I used this logic but all rows ranked as 1.

ALTER TABLE main_clinics
    ADD COLUMN visit_rank INTEGER;
UPDATE main_clinics
    SET
    visit_rank = (
        SELECT DENSE_RANK() OVER (PARTITION BY record_number ORDER BY date ASC)
        FROM main_clinics 
    );

Answer 1

If your version of SQLite is 3.33.0+ you can use a cte that returns all the ranks and with the UPDATE...FROM syntax join it to the table:

WITH cte AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY record_number ORDER BY date) AS rnk
  FROM main_clinics
)
UPDATE main_clinics AS m
SET visit_rank = c.rnk 
FROM cte AS c
WHERE c.record_number = m.record_number AND c.date = m.date;

See the demo.

For older versions of SQLite use a correlated subquery:

UPDATE main_clinics AS m1
SET visit_rank = (
  SELECT COUNT(DISTINCT date)
  FROM main_clinics AS m2
  WHERE m2.record_number = m1.record_number AND m2.date <= m1.date
);

See the demo.