Sorting your grouped results with ORDER BY clause. : Order by « Query « SQL Server / T-SQL Tutorial






  1. SQL Server / T-SQL Tutorial
  2. Query
  3. Order by
3>
4>
5>
6> CREATE TABLE Classification (
7>      Classif_ID         integer  NOT NULL PRIMARY KEY,
8>      Classification    varchar(25))
9> GO
1>
2> INSERT into Classification VALUES( 1,"Pop")
3> INSERT into Classification VALUES( 2,"Country")
4> INSERT into Classification VALUES( 3,"Alternative")
5> INSERT into Classification VALUES( 4,"Metal")
6> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3> CREATE TABLE CD (
4>      CD_ID              integer  NOT NULL PRIMARY KEY,
5>      CD_Title           varchar(40),
6>      Composer_ID        integer  NOT NULL,
7>      Classif_ID         integer  NOT NULL,
8>      SalesPrice        money,
9>      AverageCost       money)
10> GO
1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99)
2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99)
3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99)
4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99)
5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99)
6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99)
7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99)
8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99)
9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99)
10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99)
11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99)
12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99)
13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99)
14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99)
15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99)
16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99)
17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99)
18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99)
19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99)
20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99)
21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99)
22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99)
23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99)
24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99)
25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99)
26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99)
27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99)
28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99)
29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99)
30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99)
31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99)
32> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3>
4>
5> SELECT CD.Classif_ID,Classification.Classification,
6>        Count(CD.CD_ID) "Total Offerings"
7> FROM CD,Classification
8> WHERE  CD.Classif_ID = Classification.Classif_ID
9> GROUP BY CD.Classif_ID,Classification.Classification
10> ORDER BY Classification.Classification
11> GO
Classif_ID   Classification            Total Offerings
----------- ------------------------- ---------------
          3 Alternative                             3
          2 Country                                 2
          4 Metal                                   7
          1 Pop                                    19

(4 rows affected)
1>
2>
3>
4> drop table Classification;
5> drop table CD;
6> GO








1.3.Order by
1.3.1.The expanded syntax of the ORDER BY clause
1.3.2.ORDER BY Clause: ORDER BY {[col_name | col_number [ASC | DESC]]}, ...
1.3.3.Sorting your grouped results with ORDER BY clause.
1.3.4.An ORDER BY clause that sorts by one column in descending sequence
1.3.5.the order criterion may contain more than one column.
1.3.6.An ORDER BY clause that sorts by three columns
1.3.7.An ORDER BY clause that uses an alias
1.3.8.An ORDER BY clause that uses an expression
1.3.9.An ORDER BY clause that uses column positions
1.3.10.Sorting your grouped results by an aggregate.
1.3.11.NULLs Sort Last
1.3.12.order criterion contains any aggregate function.
1.3.13.Order by not null date
1.3.14.Using the TOP keyword with Ordered Results
1.3.15.do our sorting using numeric fields
1.3.16.use CAST function to sort during ordering