Jump to content


Photo

reorder a query?


  • Please log in to reply
3 replies to this topic

#1 Mike521

Mike521
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 05 December 2005 - 03:28 PM

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?

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 05 December 2005 - 06:27 PM

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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 Mike521

Mike521
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 05 December 2005 - 08:36 PM

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

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 05 December 2005 - 09:14 PM

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]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users