In a MySQL query how would I select:
- all rows, ordered by column
name - those with
name > 'f'should come first, then the rest alphabetically
so I'd get G, H, …, X, Y, Z, A, B, C …, F.
views:
95answers:
5
A:
Maybe I'm missing something, but it's
SELECT * FROM mytable WHERE name > 'F' ORDER BY name
Joachim Sauer
2010-04-26 12:24:59
you don't get all the rows with that. I need the names starting with A to come after the Zs
monk.e.boy
2010-04-26 12:27:07
Ah! So I *did* in fact miss something ...
Joachim Sauer
2010-04-26 13:05:29
+1
A:
You could make a view with 2 select query's
SELECT * FROM mytable WHERE name > 'F' ORDER BY name
and
SELECT * FROM mytable WHERE name < 'F' ORDER BY name
and combine the results
Robert
2010-04-26 12:33:05
+3
A:
SELECT
*
FROM
mytable
ORDER BY
CASE WHEN name > 'F' THEN 0 ELSE 1 END,
name
Tomalak
2010-04-26 12:33:50
Interesting... I didn't know you could order by a function that was not a result column of the query. I was going with select IF( name >= 'F', 0, 1 ) as PreSort and using THAT as the OrderBy clause
DRapp
2010-04-26 12:35:49
@monk.e.boy: In MySQL, comparisons result in `0` and `1` directly. This means you *could* get rid of the `CASE` and do `ORDER BY name < 'G', name`, but I find this is much less intuitive (and incompatible to other SQL dialects).
Tomalak
2010-04-26 12:43:20
@Tomalak, agreed, this is the most readable solution. Some of the others are smart but obfuscated :)
monk.e.boy
2010-04-26 12:45:49
@DRapp: `CASE` is not a function, it is a value returning expression. And you can sort by any value, even if calculated.
Tomalak
2010-04-26 12:46:47
you're right... bad choice of word/intent -- brain cramp with heavy allergy impact slowing down brain synapse connections... dooohhhh :)
DRapp
2010-04-26 13:12:12
A:
I think he wants an answer like
SELECT * FROM mytable WHERE name > 'F' ORDER BY SUBSTR (name, 1, 1) + 'G'
MJB
2010-04-26 12:35:43
I can't get this to work I get 'FUNCTION anonymous_databse.SUBSTR does not exist'
monk.e.boy
2010-04-26 12:40:54
Sorry -- I just did some lazy typing and this was meant as a pointer to how you might do it. I should have said for Oracle, use ORDER BY ASCII('G') + ASCII(SUBSTR (name,1,1))
MJB
2010-04-26 14:01:48
+2
A:
Using MySQL, you can use this shorter code:
SELECT * FROM tbl ORDER BY name > 'F' DESC, name
G,H,I... is greater than F, so the above condition will result to true, true sorts last, false sorts first, so just put DESC
Michael Buen
2010-04-26 12:39:22