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
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
An ORDER BY clause that sorts by three columns
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
An ORDER BY clause that uses an alias
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>
An ORDER BY clause that uses an expression
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>
An ORDER BY clause that uses column positions
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>
do our sorting using numeric fields
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
NULLs Sort Last
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>
ORDER BY Clause: ORDER BY {[col_name | col_number [ASC | DESC]]}, ...
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.
Order by not null date
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
order criterion contains any aggregate function.
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>
Sorting your grouped results by an aggregate.
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
Sorting your grouped results with ORDER BY clause.
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
The expanded syntax of the ORDER BY clause
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
the order criterion may contain more than one column.
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>
use CAST function to sort during ordering
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
Using the TOP keyword with Ordered Results
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>