SQL Server/T-SQL Tutorial/Query/Order by
Содержание
- 1 An ORDER BY clause that sorts by one column in descending sequence
- 2 An ORDER BY clause that sorts by three columns
- 3 An ORDER BY clause that uses an alias
- 4 An ORDER BY clause that uses an expression
- 5 An ORDER BY clause that uses column positions
- 6 do our sorting using numeric fields
- 7 NULLs Sort Last
- 8 ORDER BY Clause: ORDER BY {[col_name | col_number [ASC | DESC]]}, ...
- 9 Order by not null date
- 10 order criterion contains any aggregate function.
- 11 Sorting your grouped results by an aggregate.
- 12 Sorting your grouped results with ORDER BY clause.
- 13 The expanded syntax of the ORDER BY clause
- 14 the order criterion may contain more than one column.
- 15 use CAST function to sort during ordering
- 16 Using the TOP keyword with Ordered Results
An ORDER BY clause that sorts by one column in descending sequence
<source lang="sql">
3> 4> 5> create table Bankers( 6> BankerID Integer, 7> BankerName VARCHAR(20), 8> BankerContactLName VARCHAR(20), 9> BankerContactFName VARCHAR(20), 10> BankerCity VARCHAR(20), 11> BankerState VARCHAR(20), 12> BankerZipCode VARCHAR(20), 13> BankerPhone VARCHAR(20) 14> ) 15> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> SELECT BankerName, 4> BankerCity + ", " + BankerState + " " + BankerZipCode AS Address 5> FROM Bankers 6> ORDER BY BankerName DESC 7> GO BankerName Address
---------------------------------------------------------------
YUI Inc. Calgary, TY 66666 SAD Inc. Orland, PO 88888 RTY Inc. Toronto, YU 55555 QWE Inc. Regina, ER 44444 OIP Inc. San Franc, FG 77777 HJI Inc. Paris, CA 33333 GHJ Inc. Ticker, MN 00000 DFG Inc. Wisler, PL 99999 DEF Inc. New York, DE 22222 ABC Inc. Vancouver, BC 11111 (10 rows affected) 1> 2> drop table Bankers; 3> GO</source>
An ORDER BY clause that sorts by three columns
<source lang="sql">
4> 5> 6> create table Bankers( 7> BankerID Integer, 8> BankerName VARCHAR(20), 9> BankerContactLName VARCHAR(20), 10> BankerContactFName VARCHAR(20), 11> BankerCity VARCHAR(20), 12> BankerState VARCHAR(20), 13> BankerZipCode VARCHAR(20), 14> BankerPhone VARCHAR(20) 15> ) 16> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> SELECT BankerName, 4> BankerCity + ", " + BankerState + " " + BankerZipCode AS Address 5> FROM Bankers 6> ORDER BY BankerState, BankerCity, BankerName 7> GO BankerName Address
---------------------------------------------------------------
ABC Inc. Vancouver, BC 11111 HJI Inc. Paris, CA 33333 DEF Inc. New York, DE 22222 QWE Inc. Regina, ER 44444 OIP Inc. San Franc, FG 77777 GHJ Inc. Ticker, MN 00000 DFG Inc. Wisler, PL 99999 SAD Inc. Orland, PO 88888 YUI Inc. Calgary, TY 66666 RTY Inc. Toronto, YU 55555 (10 rows affected) 1> 2> 3> 4> drop table Bankers; 5> GO</source>
An ORDER BY clause that uses an alias
<source lang="sql">
4> 5> 6> create table Bankers( 7> BankerID Integer, 8> BankerName VARCHAR(20), 9> BankerContactLName VARCHAR(20), 10> BankerContactFName VARCHAR(20), 11> BankerCity VARCHAR(20), 12> BankerState VARCHAR(20), 13> BankerZipCode VARCHAR(20), 14> BankerPhone VARCHAR(20) 15> ) 16> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> SELECT BankerName, 3> BankerCity + ", " + BankerState + " " + BankerZipCode AS Address 4> FROM Bankers 5> ORDER BY Address, BankerName 6> GO BankerName Address
---------------------------------------------------------------
YUI Inc. Calgary, TY 66666 DEF Inc. New York, DE 22222 SAD Inc. Orland, PO 88888 HJI Inc. Paris, CA 33333 QWE Inc. Regina, ER 44444 OIP Inc. San Franc, FG 77777 GHJ Inc. Ticker, MN 00000 RTY Inc. Toronto, YU 55555 ABC Inc. Vancouver, BC 11111 DFG Inc. Wisler, PL 99999 (10 rows affected) 1> 2> drop table Bankers; 3> GO 1></source>
An ORDER BY clause that uses an expression
<source lang="sql">
4> 5> 6> create table Bankers( 7> BankerID Integer, 8> BankerName VARCHAR(20), 9> BankerContactLName VARCHAR(20), 10> BankerContactFName VARCHAR(20), 11> BankerCity VARCHAR(20), 12> BankerState VARCHAR(20), 13> BankerZipCode VARCHAR(20), 14> BankerPhone VARCHAR(20) 15> ) 16> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> SELECT BankerName, 3> BankerCity + ", " + BankerState + " " + BankerZipCode AS Address 4> FROM Bankers 5> ORDER BY BankerContactLName + BankerContactFName 6> GO BankerName Address
---------------------------------------------------------------
GHJ Inc. Ticker, MN 00000 QWE Inc. Regina, ER 44444 SAD Inc. Orland, PO 88888 ABC Inc. Vancouver, BC 11111 HJI Inc. Paris, CA 33333 DEF Inc. New York, DE 22222 DFG Inc. Wisler, PL 99999 YUI Inc. Calgary, TY 66666 RTY Inc. Toronto, YU 55555 OIP Inc. San Franc, FG 77777 (10 rows affected) 1> 2> drop table Bankers; 3> GO 1></source>
An ORDER BY clause that uses column positions
<source lang="sql">
4> 5> 6> create table Bankers( 7> BankerID Integer, 8> BankerName VARCHAR(20), 9> BankerContactLName VARCHAR(20), 10> BankerContactFName VARCHAR(20), 11> BankerCity VARCHAR(20), 12> BankerState VARCHAR(20), 13> BankerZipCode VARCHAR(20), 14> BankerPhone VARCHAR(20) 15> ) 16> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> SELECT BankerName, 4> BankerCity + ", " + BankerState + " " + BankerZipCode AS Address 5> FROM Bankers 6> ORDER BY 2, 1 7> GO BankerName Address
---------------------------------------------------------------
YUI Inc. Calgary, TY 66666 DEF Inc. New York, DE 22222 SAD Inc. Orland, PO 88888 HJI Inc. Paris, CA 33333 QWE Inc. Regina, ER 44444 OIP Inc. San Franc, FG 77777 GHJ Inc. Ticker, MN 00000 RTY Inc. Toronto, YU 55555 ABC Inc. Vancouver, BC 11111 DFG Inc. Wisler, PL 99999 (10 rows affected) 1> 2> drop table Bankers; 3> GO 1> 2></source>
do our sorting using numeric fields
<source lang="sql">
7> CREATE TABLE Products ( 8> ProductID int NOT NULL , 9> ProductName nvarchar (40) NOT NULL , 10> SupplierID int NULL , 11> CategoryID int NULL , 12> QuantityPerUnit nvarchar (20) NULL , 13> UnitPrice money NULL, 14> UnitsInStock smallint NULL, 15> UnitsOnOrder smallint NULL, 16> ReorderLevel smallint NULL, 17> Discontinued bit NOT NULL 18> ) 19> GO 1> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(71,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6) 2> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(72,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7) 3> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(73,"R",17,8,"24 - 777 g jars",17,171,0,5,0) 4> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(74,"L",4,7,"5 kg pkg.",10,4,20,5,0) 5> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(75,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0) 6> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(76,"L",23,1,"500 ml",18,57,1,20,0) 7> INSERT Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(77,"O",12,2,"12 boxes",13,23,0,15,0) 8> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder 3> FROM Products 4> WHERE UnitsOnOrder > 0 5> AND UnitsInStock < 10 6> ORDER BY UnitsOnOrder DESC 7> GO ProductID ProductName UnitsInStock UnitsOnOrder
---------------------------------------- ------------ ------------
74 L 4 20
(1 rows affected) 1> 2> drop table products; 3> GO</source>
NULLs Sort Last
<source lang="sql">
4> 5> 6> CREATE TABLE Customers ( 7> CustomerID nchar (5) NOT NULL , 8> CompanyName nvarchar (40) NOT NULL , 9> ContactName nvarchar (30) NULL , 10> ContactTitle nvarchar (30) NULL , 11> Address nvarchar (60) NULL , 12> City nvarchar (15) NULL , 13> Region nvarchar (15) NULL , 14> PostalCode nvarchar (10) NULL , 15> Country nvarchar (15) NULL , 16> Phone nvarchar (24) NULL , 17> Fax nvarchar (24) NULL 18> ) 19> GO 1> 2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111") 3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL) 4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444") 5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55") 6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777") 7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32") 8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99") 9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41") 10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745") 11> 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> 2> 3> SELECT 4> * 5> FROM 6> Customers 7> ORDER BY 8> CASE 9> WHEN Region IS NULL THEN 1 10> ELSE 0 11> END, 12> Region 13> GO CustomerID CompanyName ContactName ContactTitle Address City Region
PostalCode Country Phone Fax
---------------------------------------- ------------------------------ ------------------------------ ------------------------------------------------------------ --------------- ---------
---------- --------------- ------------------------ ------------------------
9 D Elizabeth Manager 23 Blvd. Tsawassen BC
T2F8M4 Canada (604) 555-4729 (604) 555-3745
9 D Elizabeth Manager 23 Blvd. Tsawassen BC
T2F8M4 Canada (604) 555-4729 (604) 555-3745
1 A Maria Sales Str. 57 Berlin NULL
12209 Germany 111-1111111 111-1111111
2 M Joe Owner Ave. 231 Vancouver NULL
05023 Mexico (222) 222-3332 NULL
3 H Thomas Sales Sq. 111 London NULL
1D00P UK (444) 444-4444 (444) 444-4444
4 B Berg Order Blv 8 Toronto NULL
00222 Sweden 4444-55 55 65 5555-55 55 55
5 S Moos Sales Fort 57 New York NULL
68306 Germany 6666-66666 6666-77777
6 F Cite Manager 24 Dalles NULL
67000 France 88.60.15.31 88.60.15.32
7 C Sommer Owner Araq, 67 Paris NULL
28023 Spain (91) 555 22 82 (91) 555 91 99
8 P Leb Owner 12 Beijing NULL
13008 France 91.24.45.40 91.24.45.41
(8 rows affected) 1> 2> 3> drop table Customers; 4> 5> GO 1></source>
ORDER BY Clause: ORDER BY {[col_name | col_number [ASC | DESC]]}, ...
<source lang="sql">
4> CREATE TABLE employee( 5> id INTEGER NOT NULL PRIMARY KEY, 6> first_name VARCHAR(10), 7> last_name VARCHAR(10), 8> salary DECIMAL(10,2), 9> start_Date DATETIME, 10> region VARCHAR(10), 11> city VARCHAR(20), 12> managerid INTEGER 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> SELECT id 4> FROM employee 5> ORDER BY id 6> 7> 8> drop table employee; 9> GO id
1
2
3
4
5
6
7
8
9
(9 rows affected) The columns in the ORDER BY clause need not appear in the SELECT list.</source>
Order by not null date
<source lang="sql">
7> CREATE TABLE Orders ( 8> OrderID int NOT NULL , 9> CustomerID nchar (5) NULL , 10> EmployeeID int NULL , 11> OrderDate datetime NULL , 12> RequiredDate datetime NULL , 13> ShippedDate datetime NULL , 14> ShipVia int NULL , 15> Freight money NULL DEFAULT (0), 16> ShipName nvarchar (40) NULL , 17> ShipAddress nvarchar (60) NULL , 18> ShipCity nvarchar (15) NULL , 19> ShipRegion nvarchar (15) NULL , 20> ShipPostalCode nvarchar (10) NULL , 21> ShipCountry nvarchar (15) NULL 22> ) 23> GO 1> 2> SELECT TOP 5 OrderID, OrderDate 3> FROM Orders 4> WHERE OrderDate IS NOT NULL 5> ORDER BY OrderDate 6> GO OrderID OrderDate
-----------------------
(0 rows affected) 1> 2> drop table Orders; 3> GO</source>
order criterion contains any aggregate function.
<source lang="sql">
5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20), 13> managerid INTEGER 14> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> SELECT region, COUNT(*) 4> FROM Employee 5> GROUP BY region 6> ORDER BY 2 DESC 7> 8> drop table employee; 9> GO region
-----------
North 3 South 2 West 2 East 2 (4 rows affected) 1> 2></source>
Sorting your grouped results by an aggregate.
<source lang="sql">
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 "Total Offerings" 11> GO Classif_ID Classification Total Offerings
------------------------- ---------------
2 Country 2
3 Alternative 3
4 Metal 7
1 Pop 19
(4 rows affected) 1> 2> drop table Classification; 3> drop table CD; 4> GO</source>
Sorting your grouped results with ORDER BY clause.
<source lang="sql">
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</source>
The expanded syntax of the ORDER BY clause
<source lang="sql">
ORDER BY expression [ASC|DESC] [, expression [ASC|DESC]] ... An ORDER BY clause that sorts by one column in ascending sequence
11> create table Bankers( 12> BankerID Integer, 13> BankerName VARCHAR(20), 14> BankerContactLName VARCHAR(20), 15> BankerContactFName VARCHAR(20), 16> BankerCity VARCHAR(20), 17> BankerState VARCHAR(20), 18> BankerZipCode VARCHAR(20), 19> BankerPhone VARCHAR(20) 20> ) 21> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> SELECT BankerName, 4> BankerCity + ", " + BankerState + " " + BankerZipCode AS Address 5> FROM Bankers 6> ORDER BY BankerName 7> GO BankerName Address
---------------------------------------------------------------
ABC Inc. Vancouver, BC 11111 DEF Inc. New York, DE 22222 DFG Inc. Wisler, PL 99999 GHJ Inc. Ticker, MN 00000 HJI Inc. Paris, CA 33333 OIP Inc. San Franc, FG 77777 QWE Inc. Regina, ER 44444 RTY Inc. Toronto, YU 55555 SAD Inc. Orland, PO 88888 YUI Inc. Calgary, TY 66666 (10 rows affected) 1> 2> 3> drop table Bankers; 4> GO</source>
the order criterion may contain more than one column.
<source lang="sql">
7> CREATE TABLE employee( 8> id INTEGER NOT NULL PRIMARY KEY, 9> first_name VARCHAR(10), 10> last_name VARCHAR(10), 11> salary DECIMAL(10,2), 12> start_Date DATETIME, 13> region VARCHAR(10), 14> city VARCHAR(20), 15> managerid INTEGER 16> ); 17> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> SELECT * 3> FROM employee 4> ORDER BY id, first_name 5> 6> 7> 8> drop table employee; 9> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1></source>
use CAST function to sort during ordering
<source lang="sql">
7> 8> create table Bankers( 9> BankerID Integer, 10> BankerName VARCHAR(20), 11> BankerContactLName VARCHAR(20), 12> BankerContactFName VARCHAR(20), 13> BankerCity VARCHAR(20), 14> BankerState VARCHAR(20), 15> BankerZipCode VARCHAR(20), 16> BankerPhone VARCHAR(20) 17> ) 18> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> SELECT * FROM Bankers 3> ORDER BY CAST(BankerZipCode AS int) 4> GO BankerID BankerName BankerContactLName BankerContactFName BankerCity BankerState BankerZipCode BankerPhone
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
0 GHJ Inc. Bit Lee Ticker MN 00000 000-000-0000
1 ABC Inc. Joe Smith Vancouver BC 11111 111-111-1111
2 DEF Inc. Red Rice New York DE 22222 222-222-2222
3 HJI Inc. Kit Cat Paris CA 33333 333-333-3333
4 QWE Inc. Git Black Regina ER 44444 444-444-4444
5 RTY Inc. Wil Lee Toronto YU 55555 555-555-5555
6 YUI Inc. Ted Larry Calgary TY 66666 666-666-6666
7 OIP Inc. Yam Act San Franc FG 77777 777-777-7777
8 SAD Inc. Hit Eat Orland PO 88888 888-888-8888
9 DFG Inc. Sad Lee Wisler PL 99999 999-999-9999
(10 rows affected) 1> 2> drop table Bankers; 3> GO</source>
Using the TOP keyword with Ordered Results
<source lang="sql">
The TOP keyword allows you to return the first n number of rows from a query based on the number of rows or percentage of rows that you define. The first rows returned are also impacted by how your query is ordered. In previous versions of SQL Server, developers used SET ROWCOUNT to limit rows returned or impacted. In SQL Server 2005, you should use the TOP keyword instead of SET ROWCOUNT, as the TOP will usually perform faster. The TOP keyword can also now be used with INSERT, UPDATE, and DELETE statements 11> 12> CREATE TABLE employee( 13> id INTEGER NOT NULL PRIMARY KEY, 14> first_name VARCHAR(10), 15> last_name VARCHAR(10), 16> salary DECIMAL(10,2), 17> start_Date DATETIME, 18> region VARCHAR(10), 19> city VARCHAR(20) 20> ); 21> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city
---------- ---------- ------------ ----------------------- ---------- --------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto
(9 rows affected) 1> 2> 3> 4> SELECT TOP 5 v.First_Name, 5> v.city 6> FROM employee v 7> ORDER BY v.city DESC, v.first_Name 8> GO First_Name city
--------------------
Jason Vancouver Alison Utown Joan Toronto James Regina James Paris (5 rows affected) 1> 2> 3> drop table employee; 4> GO 1></source>
