reorder a query?

[Mike521]

Hi all, if you have a query ordered by whatever, is it possible to check if a certain item exists, and then move that item to the top of the list?

[ryanlwh]

well you could do something like this

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] *, IF(item='WhatYouWant',1,0) AS myItem FROM tableReferences ORDER BY myItem DESC, whatever [!--sql2--][/div][!--sql3--]

[Mike521]

works perfect, thanks!!

 

edit: can you point me to some documentation that explains the AS statement you used? i've seen it used a couple of times and looks really useful but I don't quite understand how it works yet

 

hopefully I can read up on it and do things like this with no problem in the future

[ryanlwh]

I can't seem to find a documentation solely on AS. In the SELECT syntax documentation there's a brief explanation of AS. I'll try my best to explain it.

 

AS creates an alias, or nickname, to a column/literal/table. In this case, we need to order by that IF part (produces 1 and 0), and the only way to do that is to give it an alias. Since order is determined after the columns are fetched, we can use this alias to achieve quite a lot of advance stuff. For example:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] student_name, AVG(score) AS averageScore FROM students GROUP BY student_name ORDER BY averageScore DESC [!--sql2--][/div][!--sql3--]

 

You can also apply alias to tables

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] t1.id, t2.id FROM table1 AS t1, table2 AS t2 ... [!--sql2--][/div][!--sql3--]

 

Hope this helps.

 

EDIT: well here is a very very short documentation on AS: [a href=\"http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/pro...with-alias.html[/a]