Jump to content

reorder a query?


Mike521

Recommended Posts

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

Link to comment
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

Link to comment
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]

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.