How to get the row index in SQLite3?

I have created a table that has a column of registration number as primary key. I have created row_id using row_number() that is ordered by the primary key.

How can i search a registration number and get the row_id along with other information of that row?

Answer

If you have created the column row_id like:

ROW_NUMBER() OVER (ORDER BY registration_number)

then use a CTE:

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY registration_number) row_id
  FROM tablename
)
SELECT * 
FROM cte
WHERE registration_number = ?

Replace ? with the registration number that you want to search for.

Another way of getting the row_id would be with a correlated subquery:

SELECT t.*,
  (SELECT COUNT(*) FROM tablename WHERE registration_number <= t.registration_number) AS row_id 
FROM tablename AS t
WHERE t.registration_number = ?