Mega Code Archive
Select from nested select statements
<![CDATA[
3> CREATE TABLE titles(
4> title_id varchar(20),
5> title varchar(80) NOT NULL,
6> type char(12) NOT NULL,
7> pub_id char(4) NULL,
8> price money NULL,
9> advance money NULL,
10> royalty int NULL,
11> ytd_sales int NULL,
12> notes varchar(200) NULL,
13> pubdate datetime NOT NULL
14> )
15> GO
1>
2> insert titles values ('1', 'Secrets', 'popular_comp', '1389', $20.00, $8000.00, 10, 4095,'Note 1','06/12/94')
3> insert titles values ('2', 'The', 'business', '1389', $19.99, $5000.00, 10, 4095,'Note 2','06/12/91')
4> insert titles values ('3', 'Emotional', 'psychology', '0736', $7.99, $4000.00, 10, 3336,'Note 3','06/12/91')
5> insert titles values ('4', 'Prolonged', 'psychology', '0736', $19.99, $2000.00, 10, 4072,'Note 4','06/12/91')
6> insert titles values ('5', 'With', 'business', '1389', $11.95, $5000.00, 10, 3876,'Note 5','06/09/91')
7> insert titles values ('6', 'Valley', 'mod_cook', '0877', $19.99, $0.00, 12, 2032,'Note 6','06/09/91')
8> insert titles values ('7', 'Any?', 'trad_cook', '0877', $14.99, $8000.00, 10, 4095,'Note 7','06/12/91')
9> insert titles values ('8', 'Fifty', 'trad_cook', '0877', $11.95, $4000.00, 14, 1509,'Note 8','06/12/91')
10> GO
1>
2>
3>
4> SELECT rank, title_id, ytd_sales, title
5> FROM (SELECT
6> T1.title_id,
7> ytd_sales,
8> T1.title,
9> (SELECT COUNT(DISTINCT T2.ytd_sales) FROM titles AS T2
10> WHERE T1.ytd_sales <= T2.ytd_sales) AS rank
11> FROM titles AS T1) AS X
12> WHERE (ytd_sales IS NOT NULL) AND (rank <= 10)
13> ORDER BY rank
14> GO
rank title_id ytd_sales title
----------- -------------------- ----------- --------------------------------------------------------------------------------
1 1 4095 Secrets
1 2 4095 The
1 7 4095 Any?
2 4 4072 Prolonged
3 5 3876 With
4 3 3336 Emotional
5 6 2032 Valley
6 8 1509 Fifty
1>
2>
3> drop table titles;
4> GO
]]>