CODEWITHSUNDEEP
mysqlsql
xtiger
xtiger

Reputation: 1446

How to order by specific group in SQL

I have a requirement to query like this:

Display all details for Employees in the order of the follow jobs: DIRECTOR, SUPERVISOR,ENGINEER and CLERK.

The Employees table structure is like this:

(Employee_ID,FirstName,LastName,Job_ID)

The Jobs tablet structure is like this:

(Job_ID,Job_Title,Min_Salary,Max_Salary)

How can I group by specific group like this?

Thanks so much.

Upvotes: 0

Views: 59

Answers (3)

Santhosh_ms3
Santhosh_ms3

Reputation: 110

select 
      * 
  from employees employees
  join jobs jobs
    on employees.job_id = jobs.job_id
 order by case
         when job_title = 'DIRECTOR' then
          1
         when job_title = 'SUPERVISOR' then
          2
         when job_title = 'ENGINEER' then
          3
         when job_title = 'CLERK' then
          4
         else
          0 end

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If you are using MySQL (as implied by the "mysqli" tag), then use the field function:

SELECT e.*
FROM Employee e INNER JOIN
     Job J
     ON J.Job_ID = E.Job_ID
ORDER BY field(J.Job_Title, 'DIRECTOR', 'SUPERVISOR', 'ENGINEER', 'CLERK');

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

select case
         when job_title = 'DIRECTOR' then
          1
         when job_title = 'SUPERVISOR' then
          2
         when job_title = 'ENGINEER' then
          3
         when job_title = 'CLERK' then
          4
         else
          0
       end as seq,
       employees.*,
       jobs.*
  from employees
  join jobs
    on employees.job_id = jobs.job_id
 order by 1

Upvotes: 0

Related Questions