Getting row number for query

Question

I have a query which will return one row. Is there any way I can find the row index of the row I'm querying when the table is sorted?

I've tried rowid but got #582 when I was expecting row #7.

Eg:

    CategoryID  Name            
    I9GDS720K4  CatA
    LPQTOR25XR  CatB
    EOQ215FT5_  CatC
    K2OCS31WTM  CatD
    JV5FIYY4XC  CatE
--> C_L7761O2U  CatF <-- I want this row (#5)
    OU3XC6T19K  CatG
    L9YKCYAYMG  CatH
    XKWMQ7HREG  CatI

I've tried rowid with unexpected results:

SELECT rowid FROM Categories WHERE CategoryID = 'C_L7761O2U ORDER BY Name

EDIT: I've also tried J Cooper's suggestion (below), but the row numbers just aren't right.

 using (var cmd = conn.CreateCommand()) {
        cmd.CommandText = string.Format(@"SELECT (SELECT COUNT(*) FROM Recipes AS t2             WHERE t2.RecipeID <= t1.RecipeID) AS row_Num 
                FROM Recipes AS t1 
                WHERE RecipeID = 'FB3XSAXRWD'
                ORDER BY Name";
        cmd.Parameters.AddWithValue("@recipeId", id);
        idx = Convert.ToInt32(cmd.ExecuteScalar());

Why did you modify my query to `...WHERE t2.RecipeID <= t1.RecipeID`? In your question you are asking for the row number with respect to `Name` not `RecipeID` - that is why you are not getting good results, use `WHERE t2.Name <= t1.Name` instead — J Cooper, Feb 08 '12 at 22:06

J Cooper · Accepted Answer · 2012-02-05T21:55:49.627

17

Here is a way to get the row number in Sqlite:

SELECT CategoryID,
       Name,
       (SELECT COUNT(*)
        FROM mytable AS t2
        WHERE t2.Name <= t1.Name) AS row_Num
FROM mytable AS t1
ORDER BY Name, CategoryID;

edited Feb 05 '12 at 21:55

answered Feb 05 '12 at 18:53

J Cooper

4,828
3
36
39

I feel like I'm doing something wrong or missing a behavioural quirk. The row numbers are all different, depending on which ID I use, but none of them are what I'd expect. Could it have something to do with using strings (TEXT) as the ID column? – Echilon Feb 08 '12 at 20:05
Why did you modify my query to `...WHERE t2.RecipeID <= t1.RecipeID`? In your question you are asking for the row number with respect to `Name` not `RecipeID` - that is why you are not getting good results, use `WHERE t2.Name <= t1.Name` instead – J Cooper Feb 08 '12 at 22:06
This works fine, thanks. The key is to sort by the column in the WHERE clause. – Echilon Feb 11 '12 at 16:50

score 0 · Answer 2 · answered Jan 01 '14 at 20:15

Here's a funny trick you can use in Spatialite to get the order of values. If you use the count() function with a WHERE clause limiting to only values >= the current value, then the count will actually give the order. So if I have a point layer called "mypoints" with columns "value" and "val_order" then:

SELECT value, (
SELECT count(*) FROM mypoints AS my 
WHERE my.value>=mypoints.value) AS val_order
FROM mypoints 
ORDER BY value DESC;

Gives the descending order of the values. I can update the "val_order" column this way:

UPDATE mypoints SET val_order = (
SELECT count(*) FROM mypoints AS my 
WHERE my.value>=mypoints.value
);

score -3 · Answer 3 · answered Feb 05 '12 at 19:36

-3

What you are asking can be explained in two different ways, but I'm assuming you want to sort the resulting table and then number those rows according to the sort.

declare @resultrow int

select 
      @resultrow = row_number() OVER (ORDER BY Name Asc) as 'Row Number'
from Categories WHERE CategoryID = 'C_L776102U'

select @resultrow

answered Feb 05 '12 at 19:36

F43G4N

159
1
1
9

1

row_number() is not available in sqlite – J Cooper Feb 05 '12 at 21:56

Getting row number for query

3 Answers3

Linked

Related