Q
Problem solve Get help with specific problems with your technologies, process and projects.

The WITH clause

The UNION of three tables yields multiple rows of the same product_id with different prices and supplier names, and some with no item info. Can we generate a query to return a distinct set of records based on product_id with the cheapest price and the name of the supplier with that cheapest price?

The UNION of three tables yields multiple rows of the same product_id with different prices and supplier names,...

and some with no item info:

product_id  supplier  price  iteminfo
widget      Fred       100    info1
widget      Joe         90    NULL
widget      Billy       95    info3

Can we generate a query to return a distinct set of records based on product_id with the cheapest price and the name of the supplier with that cheapest price? And/or any one of the filled info fields? Or do we need to step up to PL/SQL? Thanks in advance.

If your data were in a simple table, the query you would use involves a correlated subquery:

select product_id
     , supplier
     , price
     , iteminfo
  from simpletable as T
 where price =
       ( select min(price)
           from simpletable
          where product_id = T.product_id )

The only thing we have to change to fit your specific circumstances is to replace simpletable with a derived table consisting of the result of your UNION:

select product_id
     , supplier
     , price
     , iteminfo
  from (
       select ... from table1
       union all
       select ... from table2
       union all
       select ... from table3
       ) as T
 where price =
       ( select min(price)
           from (
                select ... from table1
                union all
                select ... from table2
                union all
                select ... from table3
                ) as T1
          where product_id = T.product_id )

Note that we had to make this substitution twice. This sure is clumsy, and yes, there is a better way to write this, using the WITH clause:

with simpletable as
(
select ... from table1                 
union all                              
select ... from table2                 
union all                              
select ... from table3                 
)
select product_id
     , supplier
     , price
     , iteminfo
  from simpletable as T
 where price =
       ( select min(price)
           from simpletable
          where product_id = T.product_id )

This way of "abstracting" the derived table using the WITH clause makes the SQL manageable again. Note that the WITH clause is SQL-99, so not every database system supports it. In Oracle, you need to be on at least Oracle 9i release 2.

This was last published in September 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close