Usually a SQL result is desired either in ascending or descending order based on a column.
But what if you want the result in a random order based on your choice.
Here is an example,
Problem - Display job id and department id of departments 10, 50, 20 in that order.
Solution - At first, it seems to be a simple sql query but I have to think more on how to get the order 10, 50 and 20, its neither ascending nor descending.
Concept - Introduce a new dummy column in the select list and order by it. Select only two columns from the final result. Below is the query.
select job_id, department_id from (
select job_id, department_id, 1 from employees
where department_id = 10
union
select job_id, department_id, 2 from employees
where department_id = 50
uni
on
select job_id, department_id, 3 from employees
where department_id = 20
order by 3
);
I first did the union of individual results. This is required as I have to introduce a new column in each select query and give it a specific value so that I can order it later. Then, apply ascending order on column number 3 ( dummy column ) of the Union result.
Now, I have the result in required order but with one extra column. To remove it, I do a final select of only job id and department id from the union result.
Hope this was informative!
Thank you.
