Applying Where clause for Order by in SQL

Question

Would like to sort the following data the way allows in the order of choice of keywords based on like clause:

Data in table:

EmpId EmpLotusNotes
10001 Amit B/India
20002 Bharat C/India
30003 Kyo Jun/Japan
40004 Jee Lee/China
50005 Xavier K/USA

Data to be presented/sorted based on certain country order (Japan, China, India, USA):

EmpId EmpLotusNotes
30003 Kyo Jun/Japan
40004 Jee Lee/China
10001 Amit B/India
20002 Bharat C/India
50005 Xavier K/USA

Note: I cannot create another table that holds the country order or any other change.

Please post your SQL SELECT clause, we can't help you without that. If you could create an [SQLFiddle](http://sqlfiddle.com/), that would be awesome... — ppeterka, Jan 10 '13 at 08:39
Are the country names in a separate column or part of the column EmpLotusNotes? — Jacco, Jan 10 '13 at 08:39
This looks very similar to the previous answer: http://stackoverflow.com/questions/13196010/sql-case-order-by-specific-order — twoleggedhorse, Jan 10 '13 at 08:40
and why is Japan before China in the ordering ? Political reasons ;) ? — Raphaël Althaus, Jan 10 '13 at 08:40
Duplicate of http://stackoverflow.com/questions/8187828/sql-order-by-using-a-substring-within-a-specific-column-possible — SergeyS, Jan 10 '13 at 08:41

score 4 · Answer 1 · answered Jan 10 '13 at 08:46

This should make a trick:

SELECT
   EmpId, EmpLotusNotes
FROM 
   dbo.Table
ORDER BY
   CASE 
      WHEN EmpLotusNotes LIKE '%Japan' THEN 1
      WHEN EmpLotusNotes LIKE '%China' THEN 2 
      WHEN EmpLotusNotes LIKE '%India' THEN 3
      WHEN EmpLotusNotes LIKE '%USA' THEN 4
   END

Tim Schmelter · Answer 2 · 2013-01-10T08:46:49.153

3

Perhaps:

SELECT
   EmpId, EmpLotusNotes
FROM 
   dbo.Table
ORDER BY
   CASE WHEN EmpLotusNotes LIKE '%Japan' THEN 0 ELSE 1 END,
   CASE WHEN EmpLotusNotes LIKE '%China' THEN 0 ELSE 1 END,
   CASE WHEN EmpLotusNotes LIKE '%India' THEN 0 ELSE 1 END,
   CASE WHEN EmpLotusNotes LIKE '%USA' THEN 0 ELSE 1 END

and here's the DEMO

edited Jan 10 '13 at 08:46

answered Jan 10 '13 at 08:41

Tim Schmelter

450,073
74
686
939

score 3 · Accepted Answer · answered Jan 10 '13 at 10:47

The problem is the table violates first normal form, EmpLotusNotes should not contain the name of an employee and the country, presumably the country they work in.

You should challenge the reasons why you are not allowed to clean up the structure and the data.

See https://www.google.com.au/search?q=sql+first+normal+form+atomic

The answer, if you still cannot normalise the database after challenging, is create a query for countries, create a query to split the data in the first table into first normal form, then join the two.

An example that works for mysql follows, for MS SQL you would use CHARINDEX instead of INSTR and substring instead of substr.

select employeesWithCountries.*
, countries.sort 
from (
    select empId, empLotusNotes, substr( empLotusNotes, afterStartOfDelimiter ) country from (
        select empId
        , empLotusNotes
        , INSTR( empLotusNotes, '/' ) + 1 as afterStartOfDelimiter 
        from EmployeesLotusNotes
    ) employees
) employeesWithCountries
inner join (
    SELECT 'Japan' as country, 1 as sort
    union
    SELECT 'China' as country, 2 as sort
    union
    SELECT 'India' as country, 3 as sort
    union
    SELECT 'USA' as country, 4 as sort
) countries
on employeesWithCountries.country = countries.country
order by countries.sort, employeesWithCountries.empLotusNotes

Results.

30003    Kyo Jun/Japan   Japan    1
40004    Jee Lee/China   China    2
10001    Amit B/India    India    3
20002    Bharat C/India  India    3
50005    Xavier K/USA    USA      4

score 1 · Answer 4 · answered Jan 10 '13 at 08:46

You can use a Common Table Expression to process the raw data first and do your filtering and/or ordering on the processed data later. Something like below. The function in myCol can be changed with any kind of function, even a CASE clause.

WITH T as 
(SELECT EmpId, EmpLotusNotes, SOMEFUNCTION(EmpLotusNotes) as myCol
 FROM Table1
)
SELECT *
FROM T 
WHERE myCol = XXX
ORDER BY myCol

Applying Where clause for Order by in SQL

4 Answers4

Linked