Mike521 Posted December 5, 2005 Share Posted December 5, 2005 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? Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted December 5, 2005 Share Posted December 5, 2005 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--] Quote Link to comment Share on other sites More sharing options...
Mike521 Posted December 5, 2005 Author Share Posted December 5, 2005 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 Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted December 5, 2005 Share Posted December 5, 2005 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] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.