Sorting and saving datagrid

[mattrgs]

Hi,
I have a datagrid which downloads the contents of a MySQL database table, this is downloaded with the entries in no particular order. The table has a name column and a score column. In the datagrid I want it to be sorted by the score (descending). This can be done easily, but here comes the problem. I then want to add a column which contains a position, just by going through a loop inserting numbers as I move down the table, however when I do this it becomes clear that when I am dealing with the DG data it hasn't been sorted, it is only the stuff in the datagrid which is sorted.
So I guess what I am asking is how can you sort a datagrid and the somehow save this sorting, so that when I manipulate the data the sorting is there?
Thanks,
Matthew

[bangkok]

What's the name of your table ?

What's the name of your "score" column ?

Replace in your SQL query :

Code: Select all

select @rownum:=@rownum+1 ‘rank’, p.* from NAME_OF_YOUR_TABLE p, (SELECT @rownum:=0) r order by COLUMN_OF_SCORE desc

[mattrgs]

Thanks, I wandered if this was possible
How would I include these in my code?
Thanks
Matthew

[bangkok]

Thanks, I wandered if this was possible
How would I include these in my code?
Thanks
Matthew

What's the name of your table ?

What's the name of your "score" column ?

Replace in your SQL query :

Code: Select all

select @rownum:=@rownum+1 ‘rank’, p.* from NAME_OF_YOUR_TABLE p, (SELECT @rownum:=0) r order by COLUMN_OF_SCORE desc

[mattrgs]

I have entered it, and it returns this error code:
revdberr,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 '‘rank’, p.* from tTableName p, (SELECT @rownum:=0) r order by Score desc' at line 1

[mattrgs]

In the end I did it slightly differently, as I realised that I wanted to do the ranking in a slightly different way that isn't possible with MySQL. For anyone's future interest here is my code with the table and column name emited:

Code: Select all

put "SELECT * FROM " & tTableName & " ORDER BY tColumnName DESC" into tSQL
    
    put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData

Thanks for peoples help,
Matthew