Only first column in ORDER BY clause is sorted [closed]Sort order specified in primary key, yet sorting is executed on SELECTStorage order vs Result orderExtract data for all groups in a single SELECT statementGrouping rows by looking at two columns without considering the order and summing each separatelyorder of inserting record is mismatchingCrosstab Pivot or not?Order by custom filter without certain dataHow can I speed up an ASC sort on a column that only holds an integer between 0 and 9 across multiple millions of rows?MySQL ORDER BY columns across multiple joined tablesinconsistent distinct + order by behavior between mysql 5.6 and 5.7

Approach sick days in feedback meeting

Someone who is granted access to information but not expected to read it

Nth term of Van Eck Sequence

Does PC weight have a mechanical effect?

Co-worker is now managing my team. Does this mean that I'm being demoted?

Can a 40amp breaker be used safely and without issue with a 40amp device on 6AWG wire?

Digital signature that is only verifiable by one specific person

What is the color associated with lukewarm?

Looking for an iPhone app for working out chess problems

My players want to use called-shots on Strahd

Why not make one big CPU core?

How do credit card companies know what type of business I'm paying for?

Boss making me feel guilty for leaving the company at the end of my internship

Should I worry about having my credit pulled multiple times while car shopping?

Why can't we feel the Earth's revolution?

How would Japanese people react to someone refusing to say “itadakimasu” for religious reasons?

How can I detect if I'm in a subshell?

What made the Ancient One do this in Endgame?

How do you translate “talk shit”?

mathrm in LaTeX

Can Dive Down protect a creature against Pacifism?

How can Caller ID be faked?

Converting 3x7 to a 1x7. Is it possible with only existing parts?

What should I be aware of in buying second-hand sinks and toilets?



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


Sort order specified in primary key, yet sorting is executed on SELECTStorage order vs Result orderExtract data for all groups in a single SELECT statementGrouping rows by looking at two columns without considering the order and summing each separatelyorder of inserting record is mismatchingCrosstab Pivot or not?Order by custom filter without certain dataHow can I speed up an ASC sort on a column that only holds an integer between 0 and 9 across multiple millions of rows?MySQL ORDER BY columns across multiple joined tablesinconsistent distinct + order by behavior between mysql 5.6 and 5.7






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








4















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:



Value1 in descending order, Value2 not



Why isn't Value2 also in descending order?










share|improve this question















closed as off-topic by Colin 't Hart, kevinsky, mustaccio, Tony Hinkle, Joe Obbish May 30 at 17:25


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, kevinsky, mustaccio, Tony Hinkle, Joe Obbish
If this question can be reworded to fit the rules in the help center, please edit the question.











  • 2





    Given the row numbers in your example results, what sequence would you expect?

    – jmoreno
    May 29 at 22:43






  • 1





    What is the purpose of this? Why do you need each column sorted separately?

    – Džuris
    May 30 at 13:11

















4















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:



Value1 in descending order, Value2 not



Why isn't Value2 also in descending order?










share|improve this question















closed as off-topic by Colin 't Hart, kevinsky, mustaccio, Tony Hinkle, Joe Obbish May 30 at 17:25


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, kevinsky, mustaccio, Tony Hinkle, Joe Obbish
If this question can be reworded to fit the rules in the help center, please edit the question.











  • 2





    Given the row numbers in your example results, what sequence would you expect?

    – jmoreno
    May 29 at 22:43






  • 1





    What is the purpose of this? Why do you need each column sorted separately?

    – Džuris
    May 30 at 13:11













4












4








4


1






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:



Value1 in descending order, Value2 not



Why isn't Value2 also in descending order?










share|improve this 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:



Value1 in descending order, Value2 not



Why isn't Value2 also in descending order?







sql-server t-sql order-by






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited May 30 at 15:46









jpmc26

83021127




83021127










asked May 29 at 10:29









Pantea TourangPantea Tourang

45012




45012




closed as off-topic by Colin 't Hart, kevinsky, mustaccio, Tony Hinkle, Joe Obbish May 30 at 17:25


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, kevinsky, mustaccio, Tony Hinkle, Joe Obbish
If this question can be reworded to fit the rules in the help center, please edit the question.







closed as off-topic by Colin 't Hart, kevinsky, mustaccio, Tony Hinkle, Joe Obbish May 30 at 17:25


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, kevinsky, mustaccio, Tony Hinkle, Joe Obbish
If this question can be reworded to fit the rules in the help center, please edit the question.







  • 2





    Given the row numbers in your example results, what sequence would you expect?

    – jmoreno
    May 29 at 22:43






  • 1





    What is the purpose of this? Why do you need each column sorted separately?

    – Džuris
    May 30 at 13:11












  • 2





    Given the row numbers in your example results, what sequence would you expect?

    – jmoreno
    May 29 at 22:43






  • 1





    What is the purpose of this? Why do you need each column sorted separately?

    – Džuris
    May 30 at 13:11







2




2





Given the row numbers in your example results, what sequence would you expect?

– jmoreno
May 29 at 22:43





Given the row numbers in your example results, what sequence would you expect?

– jmoreno
May 29 at 22:43




1




1





What is the purpose of this? Why do you need each column sorted separately?

– Džuris
May 30 at 13:11





What is the purpose of this? Why do you need each column sorted separately?

– Džuris
May 30 at 13:11










6 Answers
6






active

oldest

votes


















24














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.)







share|improve this answer
































    12














    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.



    enter image description here



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






    share|improve this answer






























      7














      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);


      Dummy data



      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:
      Clean beards



      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.



      Boring






      share|improve this answer






























        2














        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:
        Output






        share|improve this answer






























          2














          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.






          share|improve this answer
































            1














            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.






            share|improve this answer





























              6 Answers
              6






              active

              oldest

              votes








              6 Answers
              6






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              24














              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.)







              share|improve this answer





























                24














                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.)







                share|improve this answer



























                  24












                  24








                  24







                  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.)







                  share|improve this answer















                  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.)








                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited May 30 at 7:53









                  Andriy M

                  16.7k63777




                  16.7k63777










                  answered May 29 at 10:44









                  Michael KutzMichael Kutz

                  2,7321212




                  2,7321212























                      12














                      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.



                      enter image description here



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






                      share|improve this answer



























                        12














                        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.



                        enter image description here



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






                        share|improve this answer

























                          12












                          12








                          12







                          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.



                          enter image description here



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






                          share|improve this answer













                          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.



                          enter image description here



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







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered May 29 at 10:37









                          Darko MartinovicDarko Martinovic

                          3163




                          3163





















                              7














                              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);


                              Dummy data



                              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:
                              Clean beards



                              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.



                              Boring






                              share|improve this answer



























                                7














                                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);


                                Dummy data



                                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:
                                Clean beards



                                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.



                                Boring






                                share|improve this answer

























                                  7












                                  7








                                  7







                                  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);


                                  Dummy data



                                  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:
                                  Clean beards



                                  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.



                                  Boring






                                  share|improve this answer













                                  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);


                                  Dummy data



                                  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:
                                  Clean beards



                                  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.



                                  Boring







                                  share|improve this answer












                                  share|improve this answer



                                  share|improve this answer










                                  answered May 29 at 22:37









                                  ap55ap55

                                  712




                                  712





















                                      2














                                      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:
                                      Output






                                      share|improve this answer



























                                        2














                                        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:
                                        Output






                                        share|improve this answer

























                                          2












                                          2








                                          2







                                          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:
                                          Output






                                          share|improve this answer













                                          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:
                                          Output







                                          share|improve this answer












                                          share|improve this answer



                                          share|improve this answer










                                          answered May 29 at 22:22









                                          DeenDeen

                                          211




                                          211





















                                              2














                                              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.






                                              share|improve this answer





























                                                2














                                                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.






                                                share|improve this answer



























                                                  2












                                                  2








                                                  2







                                                  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.






                                                  share|improve this answer















                                                  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.







                                                  share|improve this answer














                                                  share|improve this answer



                                                  share|improve this answer








                                                  edited May 30 at 11:15









                                                  Michael Green

                                                  15.1k83265




                                                  15.1k83265










                                                  answered May 29 at 10:40









                                                  shilanshilan

                                                  793




                                                  793





















                                                      1














                                                      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.






                                                      share|improve this answer



























                                                        1














                                                        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.






                                                        share|improve this answer

























                                                          1












                                                          1








                                                          1







                                                          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.






                                                          share|improve this answer













                                                          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.







                                                          share|improve this answer












                                                          share|improve this answer



                                                          share|improve this answer










                                                          answered May 30 at 17:10









                                                          LearnByReadingLearnByReading

                                                          431317




                                                          431317













                                                              Popular posts from this blog

                                                              Wikipedia:Vital articles Мазмуну Biography - Өмүр баян Philosophy and psychology - Философия жана психология Religion - Дин Social sciences - Коомдук илимдер Language and literature - Тил жана адабият Science - Илим Technology - Технология Arts and recreation - Искусство жана эс алуу History and geography - Тарых жана география Навигация менюсу

                                                              Club Baloncesto Breogán Índice Historia | Pavillón | Nome | O Breogán na cultura popular | Xogadores | Adestradores | Presidentes | Palmarés | Historial | Líderes | Notas | Véxase tamén | Menú de navegacióncbbreogan.galCadroGuía oficial da ACB 2009-10, páxina 201Guía oficial ACB 1992, páxina 183. Editorial DB.É de 6.500 espectadores sentados axeitándose á última normativa"Estudiantes Junior, entre as mellores canteiras"o orixinalHemeroteca El Mundo Deportivo, 16 setembro de 1970, páxina 12Historia do BreogánAlfredo Pérez, o último canoneiroHistoria C.B. BreogánHemeroteca de El Mundo DeportivoJimmy Wright, norteamericano do Breogán deixará Lugo por ameazas de morteResultados de Breogán en 1986-87Resultados de Breogán en 1990-91Ficha de Velimir Perasović en acb.comResultados de Breogán en 1994-95Breogán arrasa al Barça. "El Mundo Deportivo", 27 de setembro de 1999, páxina 58CB Breogán - FC BarcelonaA FEB invita a participar nunha nova Liga EuropeaCharlie Bell na prensa estatalMáximos anotadores 2005Tempada 2005-06 : Tódolos Xogadores da Xornada""Non quero pensar nunha man negra, mais pregúntome que está a pasar""o orixinalRaúl López, orgulloso dos xogadores, presume da boa saúde económica do BreogánJulio González confirma que cesa como presidente del BreogánHomenaxe a Lisardo GómezA tempada do rexurdimento celesteEntrevista a Lisardo GómezEl COB dinamita el Pazo para forzar el quinto (69-73)Cafés Candelas, patrocinador del CB Breogán"Suso Lázare, novo presidente do Breogán"o orixinalCafés Candelas Breogán firma el mayor triunfo de la historiaEl Breogán realizará 17 homenajes por su cincuenta aniversario"O Breogán honra ao seu fundador e primeiro presidente"o orixinalMiguel Giao recibiu a homenaxe do PazoHomenaxe aos primeiros gladiadores celestesO home que nos amosa como ver o Breo co corazónTita Franco será homenaxeada polos #50anosdeBreoJulio Vila recibirá unha homenaxe in memoriam polos #50anosdeBreo"O Breogán homenaxeará aos seus aboados máis veteráns"Pechada ovación a «Capi» Sanmartín e Ricardo «Corazón de González»Homenaxe por décadas de informaciónPaco García volve ao Pazo con motivo do 50 aniversario"Resultados y clasificaciones""O Cafés Candelas Breogán, campión da Copa Princesa""O Cafés Candelas Breogán, equipo ACB"C.B. Breogán"Proxecto social"o orixinal"Centros asociados"o orixinalFicha en imdb.comMario Camus trata la recuperación del amor en 'La vieja música', su última película"Páxina web oficial""Club Baloncesto Breogán""C. B. Breogán S.A.D."eehttp://www.fegaba.com

                                                              What should I write in an apology letter, since I have decided not to join a company after accepting an offer letterShould I keep looking after accepting a job offer?What should I do when I've been verbally told I would get an offer letter, but still haven't gotten one after 4 weeks?Do I accept an offer from a company that I am not likely to join?New job hasn't confirmed starting date and I want to give current employer as much notice as possibleHow should I address my manager in my resignation letter?HR delayed background verification, now jobless as resignedNo email communication after accepting a formal written offer. How should I phrase the call?What should I do if after receiving a verbal offer letter I am informed that my written job offer is put on hold due to some internal issues?Should I inform the current employer that I am about to resign within 1-2 weeks since I have signed the offer letter and waiting for visa?What company will do, if I send their offer letter to another company