Order by x then order by y column in SQL Server

Question

Consider a table like

   debit    credit  code
-----------------------------
    0       10      5
    5       0       3
    0       11      2
    0       15      1
    7       0       6
    6       0       2
    5       0       1

I need to generate a result set like this that debit come first and then ordered by code column:

debit   credit  code
----------------------------
5       0       1
6       0       2
5       0       3
7       0       6
0       15      1
0       11      2
0       10      5

Duplicate of [SQL multiple column ordering](https://stackoverflow.com/questions/2051162/sql-multiple-column-ordering) — Brien Foss, Dec 24 '17 at 07:01
Thanks, but this is not my problem solution, because I need to come debit (greater than zero) come first, then order it by debit and code — Elham Azadfar, Dec 24 '17 at 07:29
ok, please update your question to state that or be more clear for future readers benefit. Thanks! — Brien Foss, Dec 24 '17 at 07:51
@ElhamAzadfar check the answer I think cte should resolve your issue — Arijit Mukherjee, Dec 24 '17 at 08:04

Serkan Arslan · Accepted Answer · 2017-12-24T07:58:36.867

6

You can use this.

DECLARE  @MyTable TABLE(debit INT, credit INT,  code INT)

INSERT INTO @MyTable VALUES 
(0, 10, 5),
(5, 0 , 3),
(0, 11, 2),
(0, 15, 1),
(7, 0 , 6),
(6, 0 , 2),
(5, 0 , 1)

SELECT * FROM 
    @MyTable 
ORDER BY 
    (CASE WHEN debit > 0 THEN 0 ELSE 1 END) ,
    code , 
    debit

Result:

debit       credit      code
----------- ----------- -----------
5           0           1
6           0           2
5           0           3
7           0           6
0           15          1
0           11          2
0           10          5

edited Dec 24 '17 at 07:58

answered Dec 24 '17 at 07:51

Serkan Arslan

13,158
4
29
44

1

I think the result from the query won't give the result what OP is asking, in the first column order how come you get the 5,6,5 as it depends on 3rd column value – Rams Dec 24 '17 at 08:03
1

Hi Does my solution is helpful? – Arijit Mukherjee Dec 24 '17 at 08:04
@Rams first order just for separate debit >0 rows. – Serkan Arslan Dec 24 '17 at 08:18
No i was asking about my answer – Arijit Mukherjee Dec 24 '17 at 08:23
Worked like a charm for me, would you please see this question too https://stackoverflow.com/questions/47968160/group-by-then-sort-sql-server – Elham Azadfar Dec 25 '17 at 11:28

Rams · Answer 2 · 2017-12-24T15:26:05.900

3

Please use below one in order by clause you will get the output that you are looking for

  order by cast(cast(code as varchar(50)) 
                              + cast(debit as varchar(2)+ cast(credit as varchar(2) as int)

edited Dec 24 '17 at 15:26

answered Dec 24 '17 at 07:51

Rams

2,129
1
12
19

So `code` `5`, `debit` `11`, `credit` `2` would sort as equal to `5`, `1`, `12`. That doesn't sound right. – HABO Dec 24 '17 at 15:01

score 2 · Answer 3 · answered Dec 24 '17 at 07:49

2

;WITH Props AS
(
SELECT *,
    ROW_NUMBER() OVER (ORDER BY c,cc) AS RowNumber
FROM Location

)
select * from Props order by d desc,RowNumber

Try the above code

WOrking fiddle here

answered Dec 24 '17 at 07:49

Arijit Mukherjee

3,817
2
31
51

I tried it with the original data and it is not giving the expected result. – Serkan Arslan Dec 24 '17 at 08:46

Barr J · Answer 4 · 2017-12-24T07:47:26.437

0

Use this select to help you:

SELECT debit, code, credit  
FROM table a
WHERE debit > 0
or debit = 0
order by debit  code, credit desc

or

SELECT debit, code, credit  
FROM table a
WHERE debit > 0
or debit = 0
group by debit, code, credit
order by debit  code, credit desc

edited Dec 24 '17 at 07:47

answered Dec 24 '17 at 07:02

Barr J

10,636
1
28
46

Thanks for your help, but I need to debit(with number) come first and then ordered by code column, in this query credit is first – Elham Azadfar Dec 24 '17 at 07:21
Thanks again, it's thrown this exception Column 'code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause – Elham Azadfar Dec 24 '17 at 07:33
I do that but , I want to see all Debit with number greater than zero first – Elham Azadfar Dec 24 '17 at 07:38
check now to see if it helps – Barr J Dec 24 '17 at 07:47

Order by x then order by y column in SQL Server

4 Answers4

Linked