How to find row number of a record?

Question

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.

row_number() takes the results of your query and numbers them. If your query only has one row returned then row_number() will always be 1. http://msdn.microsoft.com/en-us/library/ms186734.aspx — Brandon Bertelsen, Jan 05 '12 at 07:06
There is a way. Add (or use) a unique key to the queried table and use the unique key to back-reference the result. If ProductDetailNo is unique, you could use that as a back-reference too. — Brandon Bertelsen, Jan 05 '12 at 07:13
The rows in the database is an unordered bunch or rows that you can order in the query using `order by`. So there is no "logical" row number in there to query. What do you want to do with the row number? Perhaps there are other ways of doing what you want. — Mikael Eriksson, Jan 05 '12 at 07:28

score 21 · Accepted Answer · edited Aug 13 '15 at 16:51

21

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

edited Aug 13 '15 at 16:51

Hakan Fıstık

16,800
14
110
131

answered Jan 05 '12 at 07:07

Shoaib Shaikh

4,565
1
27
35

1

try this. this will return only one row and it will contain last column Rownumber that will show its row number in the data – Shoaib Shaikh Jan 05 '12 at 07:13
Hey that is working but the inner select will run for all records since the where clause is outside – Zo Has Jan 05 '12 at 07:23
1

yes inner select will run for all records because we need row numbers for each record. then we can filter results using an outer query – Shoaib Shaikh Jan 05 '12 at 07:29
1

because row numbers are virtually created on runtime. that is why we need inner select.. if you need some other way you will have to create some triggers that will store row numbers in a column so that you can easily get the result without numbering rows at runtime. – Shoaib Shaikh Jan 05 '12 at 07:30
I got what you meant only thing which bugs me is that the inner select will run for the whole table, thanks for the explanation=) – Zo Has Jan 05 '12 at 07:34
3

@DamienJoe - The inner select may or may not have to access all rows - it will certainly need to work out what row number will be assigned, but it may be able to determine that through other means - the whole point of SQL is you tell it *what you want*, not *how to do it*, and it then tries to determine the optimal approach. – Damien_The_Unbeliever Jan 05 '12 at 07:54

score 3 · Answer 2 · answered Jan 05 '12 at 07:22

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

Hey the inner select would run for all records? – Zo Has Jan 05 '12 at 07:25 — Zo Has, Jan 05 '12 at 07:25

score 2 · Answer 3 · answered Jan 05 '12 at 07:09

2

What about this one?

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

answered Jan 05 '12 at 07:09

Sergio Tulentsev

226,338
43
373
367

That's excellent but the inner query will run for all rows in table. Do you have another solution? – Zo Has Jan 05 '12 at 07:23
2

Well, in order to number things, you have to know them all. It's the best solution for your question as it is now. – Sergio Tulentsev Jan 05 '12 at 07:25
2

Of course, the value returned by `ROW_NUMBER()` could be different between each execution of this query, given no `ORDER BY` (is that even valid?) – Damien_The_Unbeliever Jan 05 '12 at 07:29
3

Yes, without `ORDER BY`, order of returned records is undefined. – Sergio Tulentsev Jan 05 '12 at 07:31

score 2 · Answer 4 · edited Jan 05 '12 at 09:25

2

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

edited Jan 05 '12 at 09:25

musefan

47,875
21
135
185

answered Jan 05 '12 at 09:04

Gopu

55
9

score 1 · Answer 5 · answered Jan 05 '12 at 07:07

1

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!

answered Jan 05 '12 at 07:07

Vinayak Garg

6,518
10
53
80

Thanks Vinyak don't know what the downvote was for but yeah I am still hopeful some some SQL magic =) – Zo Has Jan 05 '12 at 07:14

score 1 · Answer 6 · answered Jan 05 '12 at 07:09

1

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.

answered Jan 05 '12 at 07:09

bobbymcr

23,769
3
56
67

1

Hey Bobby, there is a way with SQL. – Zo Has Jan 05 '12 at 07:35

How to find row number of a record?

6 Answers6

Linked