0

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?

GMB
  • 216,147
  • 25
  • 84
  • 135
ni7
  • 131
  • 1
  • 1
  • 6
  • The OP mentions *I have created row_id using row_number() that is ordered by the primary key* so the problem is not the same as the proposed duplicate link *"How to use ROW_NUMBER in sqlite"*. – forpas May 18 '20 at 17:35

1 Answers1

2

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 = ?
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thanks a ton! That was a great help for me.I have searched almost everything but couldn't get the idea of CTE. That worked so well. :) – ni7 May 19 '20 at 04:21