How to find record line number?

Question

Consider the sample table below

ProductDetailNo ProductDescription 224 Apples 225 Tomatoes 226 Potatoes 

How do I specify a line number for a selected line as shown below?

 RowNo ProductDetailNo Product Description 2 225 Tomatoes 

Using row_number () in my query simply returns 1 always for a single record, and not for any logical row in the database.

Thanks Damien.

Answer 1

try it

 WITH MyTable AS ( SELECT ProductDetailNo, ProductDescription, ROW_NUMBER() OVER ( ORDER BY ProductDetailNo ) AS 'RowNumber' FROM Product ) SELECT RowNumber, ProductDetailNo FROM MyTable WHERE ProductDetailNo = 225 

Answer 2

Please check it

 WITH ArticleSearch AS ( SELECT ROW_NUMBER() OVER ( ORDER BY tblProducts.ProductDetailNo ) AS RowNumber, tblProducts.ProductDetailNo, tblProducts.ProductDescription FROM tblProducts ) SELECT a.RowNumber AS SlNo, a.ProductDetailNo, a.ProductDescription FROM ArticleSearch a WHERE a.ProductDetailNo=225 

Answer 3

How about this?

 SELECT RowNo, ProductDetailNo, ProductDescription FROM (SELECT ROW_NUMBER() as RowNo, ProductDetailNo, ProductDescription FROM TheTable) as t WHERE ProductDetailNo = 225; 

Answer 4

The table row does not have a built-in row number. ROW_NUMBER() gives a row number within a specific result set. So you expect that you always get 1 when the result set contains only 1 record. If you want a row number, your table schema should include something like an IDENTITY auto-increment column.

Answer 5

  WITH productCTE AS (SELECT ROW_NUMBER() OVER(ORDER BY ProductDetailNo, ProductDescription) AS RowNo, ProductDetailNo, ProductDescription FROM tbl_Products ) SELECT * FROM productCTE WHERE RowNo = 2 

Answer 6

The line number you get is from the number of lines in the result. those. if your result has only one tuple, row no. there will always be 1.

To get the row number of the entire table, you must add an additional RowNo attribute with automatic increment to the table.

Hope this helps, but maybe SQL has an even better solution for you!