CODEWITHSUNDEEP
sqlsql-server-2012sql-order-by
Deepak Kumar Padhy
Deepak Kumar Padhy

Reputation: 4388

How to use Order By clause on a column containing string values separated by comma?

I have a table with a column named Skills which contains comma separated values for different employees like

EmpID  Skills
 1     C,C++,Oracle
 2     Java,JavaScript,PHP
 3     C,C++,Oracle
 4     JavaScript,C++,ASP
 5     C,C++,JavaScript

So I want to write a query which will order all the employees first who knows JavaScript, how can I get this result?

Upvotes: 0

Views: 4831

Answers (6)

user2398621
user2398621

Reputation: 86

This works for DB2/400:

with s (id, skill, rest) as                       
    (select id, '', sk from skills                    
     union all                                        
     select id, substr(rest, 1, locate(',',rest)-1),  
                substr(rest,locate(',',rest)+1)      
     from       s                                    
     where      locate(',',rest) > 0)                
select id, skill from s                           
where  skill = 'JavaScript'                       
order by id

Upvotes: 0

Eres
Eres

Reputation: 1729

For MySQL

select Skills from myTable 
order by case Skills 
when "Javascript" then 0 
when "Java" then 1 when "C++" then 2
end

and so on

For SQL Server

select Skills from myTable 
order by case 
when Skills="Javascript" then 1
when Skill="Java" then 2
else 3 
end

Make sure to start SQL server from 1 (That I'm not sure). Include an else before end that will show all remaining results.

For more details about SQL Server see this or see this

Upvotes: 0

bvr
bvr

Reputation: 4826

Try this

SELECT *
FROM 
(
    SELECT *
        ,CASE WHEN Skills LIKE '%JavaScript%' THEN 0 ELSE 1 END AS Rnk 
    FROM MyTable
) T
ORDER BY rnk,EmpID

DEMO

OR

SELECT * FROM #MyTable
ORDER BY CASE WHEN Skills LIKE '%JavaScript%' THEN 0 ELSE 1 END,EmpID

Upvotes: 1

Andrey Gordeev
Andrey Gordeev

Reputation: 32459

Try this:

 SELECT *
 FROM YourTable
 ORDER BY PATINDEX('%JavaScript%', Skills) DESC

But this is a bad way. You should really normalize your table.

Upvotes: 0

roman
roman

Reputation: 117400

select EmpID, Skills
from Table1
order by case when Skills like '%JavaScript%' then 0 else 1 end

Upvotes: 1

Alma Do
Alma Do

Reputation: 37365

You should not use one attribute to store multiple values. That goes against relation DB principles.

Instead of that you should create additional table to store skills and refer to employee in it. Then, your query will looks like:

SELECT 
  * 
FROM 
  employees 
  LEFT JOIN employees_skills 
    ON employee.id=employees_skills.employee_id 
WHERE 
  employees_skills='JavaScript'

Upvotes: 2

Related Questions