CODEWITHSUNDEEP
t-sqlsql-order-by
Abhineet
Abhineet

Reputation: 403

SQL Server Query Ordering Help Required

I have a table EMPLOYEE defined as below:

CREATE TABLE EMPLOYEE
(
  Employee varchar(50),
  Manager varchar(50),
  field1 bit,
  field2 bit 
) 
INSERT INTO EMPLOYEE VALUES ('Emp1','Mgr1',1,0)
INSERT INTO EMPLOYEE VALUES ('Contactor1','',0,0)
INSERT INTO EMPLOYEE VALUES ('Mgr1','',0,1)
INSERT INTO EMPLOYEE VALUES ('Mgr2','',0,1)
INSERT INTO EMPLOYEE VALUES ('Emp2','Mgr2',1,0)
INSERT INTO EMPLOYEE VALUES ('Emp3','Mgr2',1,0)
INSERT INTO EMPLOYEE VALUES ('Contractor2','',0,0)
INSERT INTO EMPLOYEE VALUES ('Emp4','Mgr1',1,0)

Assumptions:

  1. if field1 is equal to 1 then, the entity is employee.

  2. If field2 is equal to 1 then, the entity is manager.

  3. If field1 and field2 is equal to 0 then, the entity is contractor.

  4. A particular row cannot have value 1 for both field1 and field2.

The query SELECT * FROM EMPLOYEE returns following values:

enter image description here

I need a query so that I can get the result in the following format:

Desired Output

basically, the result should have the first manager followed by corresponding employees then, the next manager followed by corresponding employees and finally there should be all the Contractors.

Upvotes: 0

Views: 57

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

Just based on your sample data and expected results, this works:

select * from Employee order by
CASE WHEN Employee like 'C%' THEN 1 ELSE 0 END, --Force Contractors to the end
COALESCE(NULLIF(Manager,''),Employee), --Get employees and their managers together
CASE WHEN Employee like 'M%' THEN 0 ELSE 1 END, --Sort Managers before employees
Employee --Sort by employee name

But note my questions in my comments if you have more complex data

With field1 and field2 as defined in amended question:

select * from Employee order by
CASE WHEN field1=0 and field2=0 THEN 1 ELSE 0 END, --Force Contractors to the end
COALESCE(NULLIF(Manager,''),Employee), --Get employees and their managers together
field2 desc, --Sort Managers before employees
Employee --Sort by employee name

Upvotes: 1

gbn
gbn

Reputation: 432677

The basic ordering would be 

... ORDER BY Manager + Employee

If Manager allowed NULL, which is better IMO

... ORDER BY ISNULL(Manager, Employee)

For for SQL Server 2012, whether NULL or not

... ORDER BY CONCAT(Manager,Employee)

However, there is no discriminator to show Employee vs Contractor vs Manager differences

So using the actual string values...

ORDER BY
    CASE WHEN Employee LIKE 'Contractor%' THEN 1 ELSE 0 END,
    CONCAT(Manager,Employee)

Upvotes: 1

Related Questions