C# returning row value (rank) after Order By via SQLite

Question

My goal is to write a code order my table by "Currency". It will then using the string input of "User" and find that user after the Order By query. It will return the row number of that user which is basically the rank. Is this the most efficient way to do it? I found out I have to actually count each row which doesn't seem efficient. Thank you all in advance.

    public string rank(string user)
    {
        String sql = "SELECT user FROM '" + channel + "' ORDER BY currency DESC LIMIT 10";
        String rank = "";
        using (cmd = new SQLiteCommand(sql, myDB))
        {
            using (SQLiteDataReader r = cmd.ExecuteReader())
            {
                ///* Calculate the row number after order by *////
            }
        }
        return rank;
    }

check this: http://stackoverflow.com/questions/14023292/how-to-get-rownum-like-column-in-sqlite-iphone/19199219#19199219 and just add the limit. Posible duplicate. — Juan Ruiz de Castilla, Jan 14 '15 at 00:29

score 0 · Answer 1 · edited May 23 '17 at 12:11

The rank of the user is equivalent to the number of users who have a currency value greater than the user's own currency value.

As per this answer, https://stackoverflow.com/a/5685623/3828960, you can count the number of users using a subquery:

SELECT 
    chan1.*, 
    (
        SELECT COUNT(*) 
        FROM channel AS chan2
        WHERE chan2.currency > chan1.currency
    ) as ChanRank
FROM channel as chan1
WHERE chan1.user = 'UserName'

C# returning row value (rank) after Order By via SQLite

1 Answers1