OrderBy in SQL Server to put positive values before negative values
Select * from Table
order by
Case when sortcolumn<0 then 1 else 0 end
,sortcolumn
ORDER BY separately positive & negative numbers in MySQL statement
Can use SIGN to sort the positive numbers to the top, then take the absolute value with ABS to get the desired ASC/DESC.
SELECT * FROM theTable
ORDER BY SIGN(col) DESC, ABS(col)
EDIT
As Nahuel pointed out, the above will sort 0's to the middle between positive and negative. To instead group them with the positives, you can use a CASE instead (or, if your column is only integers, the slightly magical SIGN(col + 1))
SELECT * FROM theTable
ORDER BY
CASE WHEN col >= 0 THEN 1 ELSE 2 END,
ABS(col)
How to Order Positive and Negative Integers One after the other
Use the absolute value function when selecting your data.
SELECT my_integer
FROM my_table
ORDER BY ABS(my_integer)
This will order your data by integer magnitude.
How to Sort Amount Number Negative and Positive Number In SQL server
select * from @t t
order by expensetype,
case when amount > 0 then 1 else 2 end
result
Entity ExpenseTypeCode ExpenseType Amount
----------- --------------- ----------- -----------
15 47 airline 556
14 46 Dinner 23
13 45 drink 55
11 43 Hotel 5
16 48 Hotel -5
12 44 travel 23
If you are looking for matching pairs then something like this might be what you want
declare @t table(Entity int, ExpenseTypeCode int, ExpenseType varchar(10), Amount int)
insert into @t values
( 11, 043, 'Hotel' , 6),
( 8, 043, 'Hotel' , 5),
( 9, 043, 'Hotel' , 5),
( 10, 043, 'Hotel' , 5),
( 12, 044, 'travel' , 23),
( 13, 045, 'drink' , 55),
( 14, 046, 'Dinner' , 23),
( 15, 047, 'airline' , 556),
( 16, 048, 'Hotel' , -5),
( 17, 048, 'Hotel' , -5),
( 18, 043, 'Hotel' , -6),
( 19, 043, 'Hotel' , -6)
select t.*,row_number() over(partition by t.ExpenseType, t.amount order by t.entity) rn,t.amount as absamount
from @t t
where t.amount > 0
union all
select t.*,row_number() over(partition by t.ExpenseTypeCode, t.amount order by t.entity) rn, abs(t.amount)
from @t t
where t.amount < 0
order by t.expensetype,absamount,rn,t.amount desc
result
Entity ExpenseTypeCode ExpenseType Amount rn absamount
----------- --------------- ----------- ----------- -------------------- -----------
15 47 airline 556 1 556
14 46 Dinner 23 1 23
13 45 drink 55 1 55
8 43 Hotel 5 1 5
16 48 Hotel -5 1 5
9 43 Hotel 5 2 5
17 48 Hotel -5 2 5
10 43 Hotel 5 3 5
11 43 Hotel 6 1 6
18 43 Hotel -6 1 6
19 43 Hotel -6 2 6
12 44 travel 23 1 23
or possibly a full join
select s.*,t.* from
(
select t.*,row_number() over(partition by t.ExpenseType, t.amount order by t.entity) rn
from @t t
where t.amount > 0
) s
full join
(
select t.*,row_number() over(partition by t.ExpenseTypeCode, t.amount order by t.entity) rn
from @t t
where t.amount < 0
) t on t.expensetype = s.expensetype and t.rn = s.rn and abs(t.amount) = s.amount
order by s.expensetype
Entity ExpenseTypeCode ExpenseType Amount rn Entity ExpenseTypeCode ExpenseType Amount rn
----------- --------------- ----------- ----------- -------------------- ----------- --------------- ----------- ----------- --------------------
NULL NULL NULL NULL NULL 19 43 Hotel -6 2
15 47 airline 556 1 NULL NULL NULL NULL NULL
14 46 Dinner 23 1 NULL NULL NULL NULL NULL
13 45 drink 55 1 NULL NULL NULL NULL NULL
11 43 Hotel 6 1 18 43 Hotel -6 1
10 43 Hotel 5 3 NULL NULL NULL NULL NULL
8 43 Hotel 5 1 16 48 Hotel -5 1
9 43 Hotel 5 2 17 48 Hotel -5 2
12 44 travel 23 1 NULL NULL NULL NULL NULL
Row of the first positive number preceding a negative number in Postgres?
You can use a combination of window functions to do this. First, use count as a window function, incrementing the count only when column2>0. This will segregate your rows into the groups you'll need to pick out the positive value. We can then use first_value to grab the first row from the partition, which will be the positive value:
create table test (column1 int, column2 numeric, column3 date);
insert into test VALUES (30, 2.5, '2019-01-01'), (31, 1.5, '2019-01-02'), (28, -0.8, '2019-01-03'), (29, 1.0, '2019-01-04'), (30, 1.2, '2019-01-05'), (38, -2.1, '2019-01-06'), (37, -2.2, '2019-01-07');
SELECT column1,
column2,
first_value(column2) OVER (PARTITION BY col2_group ORDER BY column3)
FROM (
select column1,
column2,
-- Create groups of rows
count(column2) FILTER (WHERE column2>0) OVER(ORDER BY column3) as col2_group,
column3
FROM test) as sub
ORDER BY column3
;
column1 | column2 | first_value
---------+---------+-------------
30 | 2.5 | 2.5
31 | 1.5 | 1.5
28 | -0.8 | 1.5
29 | 1.0 | 1.0
30 | 1.2 | 1.2
38 | -2.1 | 1.2
37 | -2.2 | 1.2
(7 rows)
In the future, it would be very helpful if you would add the data and the expected output, separately, as text, not an image.
Related Topics
Find the Byte Size of a Row in Postgresql
When Should You Consider Indexing Your SQL Tables
Tsql Aggregate String for Group By
How to Create a Check Constraint on a Varchar Column in SQL Server Specifying a Minimum Data Length
SQL Server Compact Edition Isnull(Sth, ' ') Returns a Boolean Value
Deleting Value Using SQLite While Doing an Inner Join
SQL Select Print Out Results of Stored Procedure
How to Force Nolock Hint for SQL Server Logins
How to Make a Stored Procedure Return a "Dataset" Using a Parameter I Pass
Quickest Way to Clone Row in SQL
Choose As400 Query Records Directly from Excel
How to Generate All N-Grams in Hive
Performance of Inner Join VS Cartesian Product
Computed Column Should Result to String
How to Display Column Headers Returned by Postgresql Function