CODEWITHSUNDEEP
sortingsql-server-2008sql-order-bycolumnsorting
Ram
Ram

Reputation: 805

ORDER BY BASED ON COLUMN

I have two tables,PRODUCTS AND LOOKUP TABLES.Now i want to order the KEY Column in products table based on KEY column value in LOOKUP TABLE.

CREATE TABLE PRODUCTS
(
    ID INT,
    KEY VARCHAR(50)
)

INSERT INTO PRODUCTS 
VALUES (1, 'EGHS'), (2, 'PFE'), (3, 'EGHS'),
       (4, 'PFE'), (5, 'ABC')

CREATE TABLE LOOKUP (F_KEY VARCHAR(50))

INSERT INTO LOOKUP VALUES('PFE,EGHS,ABC')

Now I want to order the records in PRODUCTS table based on KEY (PFE,EGHS,ABC) values in LOOKUP table.

Example output:

PRODUCTS

ID   F_KEY
-----------    
2    PFE
4    PFE
1    EGHS
3    EGHS
5    ABC

I use this query, but it is not working 

SELECT * 
FROM PRODUCTS 
ORDER BY (SELECT F_KEY FROM LOOKUP)

Upvotes: 0

Views: 46

Answers (2)

PSK
PSK

Reputation: 17943

You can split the string using XML. You first need to convert the string to XML and replace the comma with start and end XML tags.

Once done, you can assign an incrementing number using ROW_NUMBER() like following.

;WITH cte 
     AS (SELECT dt, 
                Row_number() 
                  OVER( 
                    ORDER BY (SELECT 1)) RN 
         FROM   (SELECT Cast('<X>' + Replace(F.f_key, ',', '</X><X>') 
                             + '</X>' AS XML) AS xmlfilter 
                 FROM   [lookup] F)F1 
                CROSS apply (SELECT fdata.d.value('.', 'varchar(500)') AS DT 
                             FROM   f1.xmlfilter.nodes('X') AS fdata(d)) O) 
SELECT P.* 
FROM   products P 
       LEFT JOIN cte C 
              ON C.dt = P.[key] 
ORDER  BY C.rn

Online Demo

Output:

ID   F_KEY
-----------    
2    PFE
4    PFE
1    EGHS
3    EGHS
5    ABC

Upvotes: 1

CodingYoshi
CodingYoshi

Reputation: 26989

You may do it like this:

SELECT ID, [KEY] FROM PRODUCTS
ORDER BY
CASE [KEY]
  WHEN 'PFE' THEN 1
  WHEN 'EGHS' THEN 2
  WHEN 'ABC' THEN 3
END

Upvotes: 1

Related Questions