Jump to content

Archived

This topic is now archived and is closed to further replies.

Mike521

reorder a query?

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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--]

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.