SQL How to know the current number or index of the record based on date?-CodePudding

I have this table of records called sub_transactions:

id              transaction_id          date    
51                           4          2023-01-02 00:00:00
52                           4          2023-02-06 00:00:00
53                           4          2023-03-06 00:00:00

Let's say I would fetch those records with dates between Feb 5 - Mar 5, then I would display it on the front-end like:

Transaction ID     Sub Transaction ID       date
             4                      2       Feb 6, 2023

As you can see, the Sub Transaction ID column number would display the current index the record its in which is "2". Using this SQL query:

SELECT *, ROW_NUMBER() OVER(ORDER BY id) AS r_number FROM sub_transactions WHERE start_date >= '2023-02-05' AND start_date <= '2023-03-05';

Would result to:

id              transaction_id          date                         r_number
52                           4          2023-02-06 00:00:00                 1

The r_number data should be "2" since it's the second record. Another example would be fetching those records greater than Mar 5, the desired result would be:

Transaction ID     Sub Transaction ID       date
             4                      3       Mar 6, 2023

The Sub Transaction ID column number would be "3" since its the third record. If the first record from the table would be deleted, then the index resets also. What would be the best way to achieve this? Thanks.

CodePudding user response：

You could use a subselect

SELECT * FROM
(
  SELECT *, ROW_NUMBER() OVER(ORDER BY id) AS r_number 
  FROM sub_transactions
) TMP
WHERE start_date >= '2023-02-05' AND start_date <= '2023-03-05';