-2

I can't get the following to work as expected, tried what I have seen in other examples and solutions but still can't this right.

  Dim pos As String = Nothing

    Try

        OpenUserDB()

        Dim cmd43 As MySqlCommand = New MySqlCommand("SELECT COUNT(id) AS position FROM users " & _
                                            "WHERE user_id <= @UserID ORDER BY food_total DESC", connUser)
        cmd43.Parameters.AddWithValue("@UserID", UserID)

        Dim reader As MySqlDataReader = cmd43.ExecuteReader
        If reader.Read() Then
            pos = reader("position").ToString() & positionName(reader("position").ToString())
        End If

        Return pos
    Catch ex As Exception
        Dim err As New ErrorLog
        err.logError(Format(Date.Now, "dd-MM-yyy hh:mm:ss ") & ex.ToString)

        Return Nothing
    Finally
        CloseUserDB()
    End Try

table users

| id | userID | food_total |

| 1 | g01 | 84 |

| 2 | g02 | 83 |

| 3 | g03 | 34 |

I queried with UserID = g02 with expected result being 2, but got 3.

The user is suppose to be the 2nd User but it returns the wrong position. Help will be appreciated.

Diamond
  • 608
  • 7
  • 23
  • sqlfiddle some data and show your expected result – Tin Tran Apr 16 '16 at 21:08
  • "Row position" is pretty meaningless in db terms. How would you ever use it? The position is going to be based on the WHERE and ORDER BY clauses - since you order by `food_total` you get the UserId of that sequence. `COUNT(id)` should probably be `COUNT(user_id)` also, but still meaningless – Ňɏssa Pøngjǣrdenlarp Apr 16 '16 at 21:11
  • In other words, I want the rank e.g `1st, 2nd, 3rd` etx based on the `food_total` – Diamond Apr 16 '16 at 21:16

2 Answers2

1

you might want to something like this. Where you rank them all in the inner query based on food_total...the the outer query selects the information of that specific userid including the rank.

SELECT Id,userId,rank
FROM
  (SELECT Id,userID,
         @rank := IFNULL(@rank,0)+1 as rank
  FROM users
  ORDER BY food_total DESC) AS T
WHERE T.userID = 'g02'

sqlfiddle

Tin Tran
  • 6,194
  • 3
  • 19
  • 34
  • Thanks, but `rank` is not a field name. Please I need to understand this, care to explain? Thanks – Diamond Apr 17 '16 at 05:39
  • rank is not a field name in this query it's making up the rank as you order by food_total...so it'll return 2 which is what you want....did you try the sqlfiddle? – Tin Tran Apr 17 '16 at 05:41
  • look carefully at the sqlfiddle, there is no rank column in the table..it's created by the query and returns what you want – Tin Tran Apr 17 '16 at 05:42
  • you can replace the 2 occurrences of `rank` with `position` if you want...the name can be changed to anything but it's what you're after – Tin Tran Apr 17 '16 at 05:43
  • if you're still confused. try running just the inner query and see the results from that, it should make things clearer – Tin Tran Apr 17 '16 at 05:44
  • you only need to replace 'g02' with your actual userid that you're after, leave everything else as is – Tin Tran Apr 17 '16 at 05:48
  • Ok but I got this error that's why `MySql.Data.MySqlClient.MySqlException (0x80004005): Parameter '@rank' must be defined` – Diamond Apr 17 '16 at 06:02
  • 1
    read this, you might have to set your connection string to allow user variables, http://stackoverflow.com/questions/5524632/how-can-i-use-a-mysql-user-defined-variable-in-a-net-mysqlcommand – Tin Tran Apr 17 '16 at 06:12
  • Thanks for the link. The query execute fine now but `reader("rank").ToString()` always return 1 be it `g01 or g02 or g03` – Diamond Apr 17 '16 at 07:21
  • that's odd, try `SET @rank = 0;` before the SELECT query and see what happens. like execute multiple statements – Tin Tran Apr 17 '16 at 15:34
0

Somehow @TinTran solution did not work for me with vb.net, but the following work fine;

SELECT Id,userId,rank
FROM
SELECT Id,userID, @rank := IFNULL(@rank,0)+1 as rank
FROM users ORDER BY food_total DESC) AS T, (SELECT @rank:=0) R
WHERE T.userID = 'g02'
Diamond
  • 608
  • 7
  • 23