Jump to content

Subqueries From Subqueries


davej
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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 ;

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...