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
  • 195,563
  • 23
  • 62
  • 110
ni7
  • 111
  • 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
  • 145,388
  • 9
  • 31
  • 69
  • 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