CODEWITHSUNDEEP
sqloracle-database
Rob M
Rob M

Reputation: 1031

Ordering results with a union from the same table

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.

Upvotes: 0

Views: 799

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

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.)

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 5

Related Questions