Only first column in ORDER BY clause is sorted [closed]

Question

It seems like I've misunderstood the concept of 'Order by'.

I have a table with this structure and data:

CREATE TABLE TestTest (Value1 Int, Value2 Int);

INSERT INTO TestTest VALUES
(1, 10),
(2, 9),
(3, 8),
(4, 7),
(5, 6),
(6, 5),
(7, 4),
(8, 3),
(9, 2),
(10, 1)
;

With the query below:

Select Value1 , Value2
from TestTest
order by Value1 desc,Value2 desc

I expect both columns Value1 and Value2 from 10 to 1 because I use DESC for both columns.

But I see this output:

Why isn't Value2 also in descending order?

Answer 1

You are ordering rows of data, not each column separately.

The row (10, 1) comes before the row (1, 10) because of your ORDER BY clause.

The Value2 only comes into play when there is a tie on the first column.

As further explained by Hellion, as far as the database is concerned, the pair (10, 1) is an indivisible unit: it's not two values, it's one set (that happens to contain two values). You can't just break the set into pieces and swap the pieces into different sets willy-nilly. As I said (and as Darko shows in another answer), the ORDER BY clause sorts by the first specified column (Value1), and then if there is more than one row with the same number in Value1, it sorts that sub-set of rows by Value2.

As another illustration, you can also consider the example suggested by A C:

See also the way the dictionary sorts words: AA, AB, AC, BA, BB, BC, CA, CB, CC... Now replace the first letter with your first column value (even if it's more than one digit) and the second letter with the second column value and there you go - same principle. (Yes, I sorted ASCending for clarity -- DESCending sorted dictionaries are hard to find.)

Answer 2

For example, if you have multiple rows with value1 equals to 10, then the second part of ORDER clause will take place as you expected.

Consider the following snippet, where three rows exist that have the value1 equal to 10.

DECLARE @test AS TABLE
( 
                       value1 int, value2 int
);

INSERT INTO @test
VALUES( 10, 1 ), ( 9, 2 ), ( 8, 3 ), ( 7, 4 ), ( 6, 5 ), ( 5, 6 ), ( 4, 7 ), ( 3, 8 ), ( 2, 9 ), ( 1, 10 ), ( 10, 2 ), ( 10, 3 );

SELECT *
FROM @test
ORDER BY value1 DESC, value2 DESC;

The output is shown in the image below.

More info on SELECT - ORDER BY Clause (Transact-SQL)

Answer 3

Let's use a more illustrative example.

Create a table with some dummy data:

CREATE TABLE [PurchaseHistory](
    [CustomerName] VARCHAR(50),
    [Item] VARCHAR(50),
    [Quantity] INTEGER
);

INSERT INTO [PurchaseHistory]
([CustomerName],[Item],[Quantity])
VALUES
('ZZ Top','Shampoo (Beard Wash) 16oz',3),
('ABC Stores','Fruit Punch 8oz',30),
('Nicolas Cage','Beekeeper suit',1);

If you selected this with Item/Quantity DESC, what do you expect to come out?

SELECT [CustomerName],[Item],[Quantity]
FROM [PurchaseHistory]
ORDER BY
    [CustomerName] ASC,
    [Item] DESC,
    [Quantity] DESC
;

With the example in your question, it seems like you expect it to come out like this:

Suddenly your records now indicate that ABC stores bought 30 bottles of Beard Wash, Nicolas Cage was kind of thirsty one day, and ZZ Top is sharing a single bee suit between them.

Instead, when using ORDER BY, the values in a row are kept together, resulting in the correct data set where ABC stores is still buying 30 bottles of fruit punch, Nick Cage gets his beekeeper suit, and ZZ Top continue to have clean facial hair.

Answer 4

You can't really order by two columns simultaneously with ORDER BY alone, but to get to your desirable output you could do something within Common Table Expressions:

CREATE TABLE #test
(
int1 int
, int2 int
)

INSERT INTO #test (int1, int2)

VALUES (1,10), (2,9), (3,8), (4, 7), (5, 6), (6,5), (7, 4), (8, 3), (9, 2), (10, 1)
;WITH CTE
AS
(
SELECT 
int1
, int2
, ROW_NUMBER() OVER (order by int1 DESC) int_1
, ROW_NUMBER() OVER (order by int2 DESC) int_2
FROM #test
) 
SELECT c.int1, int1.int2 FROM cte c
   JOIN CTE int1 ON c.int_1 = int1.int_2

DROP TABLE #test

Output:

Answer 5

Order by sorts the records not the column values.

If you specify multiple columns, the result set is sorted by the first column and then, for rows that have the same value in the first column, that sorted result set is sorted by the second column, and so on.

Answer 6

You are thinking of a columnar database/table (a columnar database stores data in columns instead of rows). Vast majority of RDBMs out there and their default setting stores data as rows (row-oriented database). In your case, the RDBMS first sorts the rows based on the descending values of the first column, as prescribed in your SQL statement. Then, it sorts it based on the second one (Val2). However, in your case, this is useless since you only have two columns. Consider, however, a table with 300 columns, in such a case you want a first order and second order sort. In Excel language what you are doing is called first order, second order, etc.