SQL Query with specific Order By format [closed]

Question

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 6 years ago.

I have a table with following values

    OperationCode   OperationDesc   OperationOrder
    Repl        Remove          1
    R&I         Remove          1
    Ovrh        Remove          1
    Refn        Prep            1
    Repl        Replace         2
    R&I         Install         2
    Ovrh        Install         2
    Refn        Paint           2

I want to write a query to get following result order. See in OperationOrder Column

    OperationCode   OperationDesc   OperationOrder
    Repl            Remove      1
    Repl            Replace     2
    R&I             Remove      1
    R&I             Install     2
    Ovrh            Remove      1
    Ovrh            Install     2
    Refn            Prep        1
    Refn            Paint       2

I am using sql server 2005.

Answer 1

Here is SQLFiddel Demo

Below is the Sample Query Which You can try

SELECT * FROM temp
ORDER BY 
CASE OperationCode 
     WHEN 'Repl' THEN 1
     WHEN 'R&I' THEN 2
     WHEN 'Ovrh' THEN 3
     WHEN 'Refn' THEN 4
END, OperationOrder

Answer 2

Looks like you want to use ORDER BY:

SELECT OperationId, OperationCode, OperationDesc, OperationOrder
FROM YourTable
ORDER By OperationCode ASC

Answer 3

IF you need a fixed order of the "OperationCode" a.k.a:
Repl < R&I < Ovrh < Refn

SELECT *  
FROM YourTable  
ORDER By   
    OperationCode = 'Repl' DESC,  
    OperationCode = 'R&I'  DESC,  
    OperationCode = 'Ovrh' DESC,  
    OperationCode = 'Refn' DESC

"OperationCode = 'Repl'" will return a 0/1 and if you order it with DESC this will put, 'Repl' values first end everything else after that.

Answer 4

I think you want

SELECT 
    ROW_NUMBER() OVER (ORDER BY OperationCode, OperationOrder) as OperationId, 
    OperationCode, 
    OperationDesc, 
    OperationOrder 
FROM SomeTable 
ORDER BY OperationCode, OperationOrder

as it looks like your OperationId has changed order in each result set and therefore must be an auto-generated number