Ordering results with a union from the same table

Question

I need to show a group of results first in a list and then show the rest of the results from the table below.

I've tried SQL: how to use UNION and order by a specific select? but it doesn't appear to work in my case.

My query looks like this

SELECT * FROM (
    SELECT id, display as ordered
      FROM table
     WHERE id in (...) --these need to be first
    UNION
    SELECT id, display
      FROM table
     WHERE id not in (...) --these need to be at the end
)
ORDER BY ordered

My results are coming back all ordered by display regardless of what I do.

I'm using Oracle, btw.

Thanks for the help.

score 5 · Accepted Answer · answered Mar 01 '17 at 20:35

You need to include numbers to explicitly to get the ordering. This query orders the first result set first and then the second. In each group the results are again ordered by id. (remove it if not required)

SELECT id,display FROM (
    SELECT id, display,1 as ordered
      FROM table
     WHERE id in (...) --these need to be first
    UNION
    SELECT id, display,2 
      FROM table
     WHERE id not in (...) --these need to be at the end
)
ORDER BY ordered,id

@RobM Would you consider accepting the answer then? More users will be benefited this way. — Giorgos Altanis, Mar 01 '17 at 21:05

Gordon Linoff · Answer 2 · 2017-03-01T21:11:32.227

0

I think you should do this as:

select id, display
from table
order by (case when id in (. . .) then 1  -- first list
               when id in (. . .) then 3  -- last list
               else 2                     -- everything else
          end);

There is no need for union or union all. Just a single order by expression.

You should include a where clause if you don't want all the ids.

(I find your question a bit unclear about what constitutes the lists. You can use not in if that really is appropriate.)

edited Mar 01 '17 at 21:11

answered Mar 01 '17 at 21:09

Gordon Linoff

1,242,037
58
646
786

...and, perhaps, if needed, add id (or whatever else) as secondary ordering criteria – Mar 01 '17 at 21:10

Ordering results with a union from the same table

2 Answers2