How to sort one column and then another within it in SQL?

Question

I have this table

state   num
-------------------------
ca      20
ny      30
ca      50
ca      10
ny      70
ny      90

What I want is to produce this:

state   num
-------------------------
ca      50
ca      20
ca      10
ny      90
ny      70
ny      30

Basically, I want the state column to be sorted, and then within each state, have the num sorted as well. How can I achieve this?

Your results don't align with your sample data, is that just a typo? — Stu, May 06 '22 at 16:51

score 2 · Answer 1 · answered May 06 '22 at 16:44

2

specify both column you want to sort one separated by a comma :

select state, num from table_name order by 1, 2 desc

or

select state, num from table_name order by state, num desc

(seems like you wanted the first column sorted on asc way while the second one in desc way)

answered May 06 '22 at 16:44

Thierry

This is not correct, I need the num and the state to match. They should be part of the same row. – sweetlemon May 06 '22 at 17:03
Sorting by position is something that many people dislike for reducing maintainability. (E.g. add a column to the select clause or remove it and you suddenly end up with your results in the wrong order.) – Thorsten Kettner May 06 '22 at 17:15
yes, that's why i've given both ways of ordering :) not all queries finish in some code you have to maintain. For a quick exec on a console, the first option is handy, even more when the select is using some complex expressions like truncate on dates etc :) – Thierry May 06 '22 at 18:01
@sweetlemon you're remarks doesn't make any sense. are `state` and `num` two columns from different tables that you are joining ? – Thierry May 06 '22 at 18:05

score 2 · Answer 2 · answered May 06 '22 at 16:51

2

SELECT * FROM yourtablename ORDER BY state, num DESC

answered May 06 '22 at 16:51

andrea margotti

This is not correct, I need the num and the state to match. They should be part of the same row. – sweetlemon May 06 '22 at 17:03
2

@sweetlemon: This doesn't seem to make sense. This query selects rows from the table (and in each row there is a num and a state) and the query orders the rows by state in ascenting order and within each state by num descendingly. Exactly as you described the problem and as your expected result suggests. – Thorsten Kettner May 06 '22 at 17:13

2 Answers2