Subqueries From Subqueries

[davej]

Over on Yahoo Answers someone posted this PL/SQL code, which was claimed to be functional:

select max(c_id), req_id, exp_datefrom(select req_id, max(exp_date)from tablegroup by req_id) t1join(select c_id, req_id, exp_datefrom table) t2on (t1.exp_date = t2.exp_date)group by req_id, exp_date

The stated purpose of the code is to eliminate duplicate REQ_ID rows and to select only rows with the most recent date and highest C_ID in a table of the form. C_ID | REQ_ID | EXP_DATE24 211 2012-7-1539 211 2012-8-1526 211 2012-9-2036 222 2012-9-20 What strikes me about this code is the use of a subquery in the FROM line. I have never written a subquery in that form. In fact I did not know it was possible. In SqlServer I tried this preliminary test;

SELECT *FROM( SELECT C_ID  FROM mytable ) ;

And it produced a syntax error. So what is the truth here?

[justsomeguy]

Your example doesn't give the table a name. Tables need a name. That subquery creates a temporary table that it can use to select data from. In the original code, the first subquery creates a temporary table called t1, and it's joined with another temporary table called t2 that gets created from the other subquery. SELECT *FROM( SELECT C_ID FROM mytable ) AS temp_table_name ;

[davej]

Your example doesn't give the table a name. Tables need a name.

Yes indeed, that makes it work. I had never seen that form of subquery before. Thanks!