SQL Table sorting by multiple columns

user_14693337 0
Viewed 267

I have a table i need to sort but im not certain on what the right ORDER BY statement would be. Here is an example:

we need to sort by ASCENDING Name, then DESCENDING Values, then ASCENDING Location. But if rows have the same Name and Location then we sort by ASCENDING Values.

BEFORE SORTING:

Name Location Values
A T 1000
A U 2000
A V 2500
B X 3000
B Y 4000
C X 1000
C T 500
C U 1000
C V 6000
D Z 9000
D Z 7000
D X 300

AFTER SORTING:

Name Location Values
A V 2500
A U 2000
A T 1000
B Y 4000
B X 3000
C V 6000
C U 1000
C X 1000
C T 500
D Z 7000
D Z 9000
D X 300

Sorry its a bit lengthy! but all situations are described in the tables above!

sql-order-by postgresql sql
1 Answers

Using a window function to get a sort key for a group

select *
from (
  select *, min(Values) over(partition by Name, Location) gmin
  from t
) t
order by Name, gmin desc, Location, values
user_62199790
Related