Menu
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
488
You can also ORDER BY two or more columns, which creates a nested sort . The default is still ascending, and the column that is listed first in the ORDER BY clause takes precedence. The following query and Figure 3 and the corresponding query results show nested sorts.
If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY .
After the ORDER BY keyword, add the name of the column by which you'd like to sort records first (in our example, salary). Then, after a comma, add the second column (in our example, last_name). You can modify the sorting order (ascending or descending) separately for each column.
C) Sort rows by column's positions example SELECT name, credit_limit FROM customers ORDER BY 2 DESC, 1; In this example, the position of name column is 1 and credit_limit column is 2. In the ORDER BY clause, we used these column positions to instruct the Oracle to sort the rows.
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
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
