0

MySql version is 5.0.45. First,I need to user PARTITION,RANK(),ROW_NUMBER() operator in query. But this version isn't support. So I must to use this ways instead:

SELECT t.*, 
   @rownum := @rownum + 1 AS rank
FROM YOUR_TABLE t, 
   (SELECT @rownum := 0) r

But MySql.Data.MySqlClient seems not support the operator(:=)

Using MySql.Data.MySqlClient
        Dim con As MySqlConnection
        Dim cmd As MySqlCommand
        Dim myAdapter As MySqlDataAdapter
        con = New MySqlConnection
        con.ConnectionString = "...."
        con.Open()
        cmd = New MySqlCommand(strQuery, con)
        myAdapter = New MySqlDataAdapter
        myAdapter.SelectCommand = cmd
        myAdapter.SelectCommand.Parameters.AddWithValue("@rownum", 0)
        myAdapter.Fill(datatable)

        con.Close()
    End Using

It has some error like this

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= 0'

How to set variable in SELECT?

Fame th
  • 1,018
  • 3
  • 17
  • 37
  • MySQL 5.0? Wow... back to 2005. I don't know if it's still supported, but I would strongly advice you upgrade to a newer version. – The Impaler May 12 '21 at 19:20
  • Solution in the linked question. But I strongly suggest you upgrade to the current version of MySQL that supports window functions, if you need that feature. – Bill Karwin May 12 '21 at 23:13
  • Client don't want to upgrade their database server. – Fame th May 13 '21 at 02:33

0 Answers0