0

I have a table of Verticals which have names, except one of them is called 'Other'. My task is to return a list of all Verticals, sorted in alpha order, except with 'Other' at the end. I have done it with two queries, like this:

String sqlMost = "SELECT * from core.verticals WHERE name != 'Other' order by name";
String sqlOther = "SELECT * from core.verticals WHERE name = 'Other'";

and then appended the second result in my code. Is there a way to do this in a single query, without modifying the table? I tried using UNION 

(select * from core.verticals where name != 'Other' order by name)
UNION (select * from core.verticals where name = 'Other');

but the result was not ordered at all. I don't think the second query is going to hurt my execution time all that much, but I'm kind of curious if nothing else.

AZep
  • 45
  • 7

1 Answers1

0

UNION ALL is the usual way to request a simple concatenation; without ALL an implicit DISTINCT is applied to the combined results, which often causes a sort. However, UNION ALL isn't required to preserve the order of the individual sub-results as a simple concatenation would; you'd need to ORDER the overall UNION ALL expression to lock down the order.

Another option would be to compute an integer order-override column like CASE WHEN name = 'Other' THEN 2 ELSE 1 END, and ORDER BY that column followed by name, avoiding the UNION entirely.

Community
  • 1
  • 1
Jeffrey Hantin
  • 35,734
  • 7
  • 75
  • 94
  • Thanks for the quick answer! I had thought of your second answer (though not the CASE to auto-set the value), but wondered if it could be done with the existing table definition. Edited my original question accordingly. re: your first suggestion, wouldn't ordering the UNION ALL just put the 'Other' back into alpha order? I just tried UNION ALL without the overall ordering and it did what I wanted, but if it's not guaranteed then I can't count on it. – AZep Jan 28 '16 at 01:54
  • You'd need the synthetic column to guarantee the special ordering you're looking for. You can do that in the query without modifying the base table, though. – Jeffrey Hantin Jan 28 '16 at 02:49