Select from nested select statements : Inline view « Subquery « SQL Server / T-SQL Tutorial





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










8.3.Inline view
8.3.1.Creating a Derived Table
8.3.2.Subqueries and Derived Tables
8.3.3.SELECT FROM (query that returns a regular result set) AS JOIN
8.3.4.A query that required a derived table.
8.3.5.In-Line Views (Derived Tables)
8.3.6.A query that uses a correlated subquery in its SELECT clause to retrieve the most recent Billing for each Banker
8.3.7.Queries in the FROM Clause
8.3.8.Select from nested select statements
8.3.9.Join with (inline)Table Expressions
8.3.10.Finding First Employee Using a Derived Table