CODEWITHSUNDEEP
sqlsql-server
Zo Has
Zo Has

Reputation: 13028

How to find row number of a record?

Consider example table below

ProductDetailNo    ProductDescription
      224                Apples
      225                Tomatoes
      226                Potatoes

How do I list the row number for a selected row like below ?

RowNo    ProductDetailNo          Product Description
  2         225                Tomatoes

Using row_number() in my query just returns 1 always for a single record no mater what the logical row is in the database.

Thanks, Damien.

Upvotes: 15

Views: 84909

Answers (6)

Shoaib Shaikh
Shoaib Shaikh

Reputation: 4585

try this

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

Upvotes: 22

Gopu
Gopu

Reputation: 55

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

Upvotes: 2

Renju Vinod
Renju Vinod

Reputation: 254

Please Check This 

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

Upvotes: 3

bobbymcr
bobbymcr

Reputation: 24167

There is no inherent row number for a table row. ROW_NUMBER() gives you the number of the row only within a specific result set. So it is the expected result 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 auto-incrementing IDENTITY column.

Upvotes: 1

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230336

What about this one?

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

Upvotes: 3

Vinayak Garg
Vinayak Garg

Reputation: 6616

The row number you receive is from number of the rows of result. i.e. if your result has just one tuple, the row no. will always be 1.

To get row number of the entire table, you should add a extra attribute, a RowNo with auto increment to your table.

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

Upvotes: 1

Related Questions