First, the setup:
CREATE TABLE `user` ( `user` varchar(255) DEFAULT NULL, `host` varchar(255) DEFAULT NULL, `sort` int(11) DEFAULT NULL ); INSERT INTO `user` (`user`, `host`, `sort`) VALUES ('','%',8), ('testUser','%',7), ('','%localhost',9), ('testUser','%localhost',5), ('','%localhost%',10), ('testUser','%localhost%',6), ('','localhost',2), ('testUser','localhost',1), ('','localhost%',4), ('testUser','localhost%',3);
Now, the quiz:
SELECT * FROM user ORDER BY ___
Fill in the blank to get the following output. Difficulty: the `sort` field may not appear anywhere in the query.
+----------+-------------+------+ | user | host | sort | +----------+-------------+------+ | testUser | localhost | 1 | | | localhost | 2 | | testUser | localhost% | 3 | | | localhost% | 4 | | testUser | %localhost | 5 | | testUser | %localhost% | 6 | | testUser | % | 7 | | | % | 8 | | | %localhost | 9 | | | %localhost% | 10 | +----------+-------------+------+
is this a trick question?
SELECT * FROM user ORDER BY 3
Comment by JJ — April 16, 2007 @ 5:47 pm
That’s still using the field, even though it doesn’t use the word “sort”. For the quiz, just use that sort field as a check to know you got it right.
Comment by snoyes — April 16, 2007 @ 6:42 pm
I doubt this is the most elegant solution, but here is a working one. If you are looking for a solution using ordering by conditionals, is there a less complex ordering method?
SELECT * FROM user
ORDER BY
IF(POSITION(‘%’ IN host) = 1, 1, 0),
IF(POSITION(‘%’ IN host) = 10, 1, 0),
IF(user != ‘testUser’, LENGTH(host), 0),
IF(host != ‘%’, LENGTH(host), 11);
Comment by Joel — April 16, 2007 @ 9:37 pm
I guess this one would be more accurate to find correct host, but doesn’t work as you’d expected ;)
select * from user
order by
if(locate(‘%’, host) = 0, 0, IF(locate(‘%’, host) = 1, 2, 1)),
user desc,
if(substr(host, -1) = ‘%’, IF(host = ‘%’, 2, 1), 0);
Comment by Michal — April 17, 2007 @ 11:37 am