|
You must also use the ORDER BY clause, and all columns in the Compute...BY clause must appear in the ORDER BY clause.
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> SELECT CD.Classif_ID,Classification.Classification,
4> CD.SalesPrice
5> FROM CD,Classification
6> WHERE CD.Classif_ID = Classification.Classif_ID
7> ORDER BY Classification.Classification
8> COMPUTE MIN(CD.SalesPrice) by Classification.Classification
9> GO
Classif_ID Classification SalesPrice
----------- ------------------------- ---------------------
3 Alternative 14.9900
3 Alternative 14.9900
3 Alternative 15.9900
min
---------------------
14.9900
Classif_ID Classification SalesPrice
----------- ------------------------- ---------------------
2 Country 15.9900
2 Country 14.9900
min
---------------------
14.9900
Classif_ID Classification SalesPrice
----------- ------------------------- ---------------------
4 Metal 14.9900
4 Metal 14.9900
4 Metal 12.9900
4 Metal 11.9900
4 Metal 14.9900
4 Metal 14.9900
4 Metal 13.9900
min
---------------------
11.9900
Classif_ID Classification SalesPrice
----------- ------------------------- ---------------------
1 Pop 16.9900
1 Pop 14.9900
1 Pop 14.9900
1 Pop 14.9900
1 Pop 16.9900
1 Pop 13.9900
1 Pop 12.9900
1 Pop 14.9900
1 Pop 16.9900
1 Pop 9.9900
1 Pop 25.9900
1 Pop 14.9900
1 Pop 11.9900
1 Pop 19.9900
1 Pop 16.9900
1 Pop 14.9900
1 Pop 11.9900
1 Pop 9.9900
1 Pop 9.9900
min
---------------------
9.9900
1>
2> drop table Classification;
3> drop table CD;
4> GO
|