How to give priority to a single employee while sorting the employee name in sql?

Question

I have table like TB1:

Emp_name  Dept_name  salary
Girish    BB          20000
Bhanu     AA          10000
Mahesh    CC          10000
Seema     YY          30000

The output I need is:

Emp_name  Dept_name  salary
Mahesh    CC          10000
Bhanu     AA          10000
Girish    BB          20000
Seema     YY          30000

Here what I have done is gave priority to 'Mahesh' and rest all the employees are sorted asc.

I tried this query:

SELECT *
FROM Employee
ORDER BY CASE
    WHEN Emp_name LIKE '%Mahesh%' THEN 1
    WHEN Emp_name LIKE '%' THEN 2
    ELSE 3
end;

Thank you for your time.

This query sorts Mahesh first, then all the others, then nulls. There is no alphabetic order taking place, because you have not told the DBMS to do this. If you want to sort the other names alphabetically, add that to the `ORDER BY` clause: `ORDER BY CASE ... END, emp_name`. — Thorsten Kettner, Nov 08 '21 at 07:20
https://stackoverflow.com/questions/1250156/how-do-i-return-rows-with-a-specific-value-first/ — Rob Farley, Nov 08 '21 at 20:48

Zhorov · Accepted Answer · 2021-11-08T07:53:45.997

1

Simply use the correct ORDER BY clause:

Sample data:

SELECT *
INTO Employee
FROM (VALUES
   ('Girish', 'BB', 20000),
   ('Bhanu',  'AA', 10000),
   ('Mahesh', 'CC', 10000),
   ('Seema',  'YY', 30000)
) Employee (Emp_name, Dept_name, Salary)

Statement:

SELECT *
FROM Employee
ORDER BY 
   CASE
      WHEN Emp_name LIKE '%Mahesh%' THEN 1
      ELSE 2
   END,
   Emp_name ASC

edited Nov 08 '21 at 07:53

answered Nov 08 '21 at 06:50

Zhorov

28,486
6
27
52

Ali Fidanli · Answer 2 · 2021-11-08T06:56:52.440

0

My solution :

   SELECT Emp_name, Dept_name, Salary FROM (
select T.*,
       CASE
         WHEN Emp_name = 'Mahesh' THEN
          0
         ELSE
          1
       END SORT1
  from (VALUES
   ('Girish', 'BB', 20000),
   ('Bhanu',  'AA', 10000),
   ('Mahesh', 'CC', 10000),
   ('Seema',  'YY', 30000)
) T (Emp_name, Dept_name, Salary)) TT
  ORDER BY TT.SORT1,TT.Emp_name

edited Nov 08 '21 at 06:56

answered Nov 08 '21 at 06:51

Ali Fidanli

1,342
8
12

Yasin · Answer 3 · 2021-11-08T09:46:55.597

0


SELECT *
FROM Employee
ORDER BY 
   CASE
      WHEN Emp_name Like '%Mahesh%' THEN 1
      ELSE 2
   END,
   Dept_name ASC, salary ASC

edited Nov 08 '21 at 09:46

answered Nov 08 '21 at 06:58

Yasin

103
1
1
9

No. This selects all employees whose names are not null and then sorts them aphabetically. In detail: First, `UNION ALL` takes place, but as tables (including query results) are unordered data sets, Manesh can be anywhere in that intermediate result. Then the `ORDER BY` clause works on the whole data set putting Manesh where it belongs in the alphabet. – Thorsten Kettner Nov 08 '21 at 07:30
@ThorstenKettner thank you for your describe . but he wanted this output. this query first puts "Manesh" in the first row. Then sort the rest of the rows. my score is low. please give me a positive vote. – Yasin Nov 08 '21 at 07:36
You haven't understood what I have decribed. No, your query does not put Manesh first. It puts it in order with all the others (sorry, it's not alphabetical order as mistakenly mentioned above, but department and salary order in your query). Please read again what I have written and understand your misconception. Ordering is an important concept in SQL and you have not fully understood it yet. – Thorsten Kettner Nov 08 '21 at 08:59
@ThorstenKettner. Thank you very much for your help. It is true . You are right. I corrected it. My query, although it has the this output, but is wrong and its speed is very low. please give me positive vote. – Yasin Nov 08 '21 at 09:52

score -1 · Answer 4 · edited Nov 08 '21 at 07:28

-1

Run this query on TB1 table

CREATE TABLE TB2
SELECT * FROM TB1 ORDER BY salary ASC;

Where TB2 created run this query

SELECT * FROM TB2 ORDER BY Dept_name='cc' DESC;

edited Nov 08 '21 at 07:28

Dale K

25,246
15
42
71

answered Nov 08 '21 at 07:02

Himanshu Rawat

1
1

I think you have misunderstood the request. The OP wants Manesh first, then all other names in alphabetical order it seems. Moreover, a table is considered an unordered data set. The rows in TB2 are hence not ordered by salary only because you happened to use a query with an `ORDER BY` clause when populating it. And `ORDER BY Dept_name='cc'` would override this anyway. And are you sure SQL Server can order by a boolean expression? I think it doesn't. – Thorsten Kettner Nov 08 '21 at 07:38

How to give priority to a single employee while sorting the employee name in sql?

4 Answers4