Jump to content

I don't understand this correlated subquery


david1972
 Share

Recommended Posts

My question: Why does the inner query need a table alias? 

Q: Use a correlated subquery to return one row per vendor 
representing the vendor's largest invoice. 

Each row should include vendor_id, vendor_name, invoice_total,
and invoice_date. Order by invoice_totol in DESC.

______________________________________________________________________
SELECT v.vendor_id, vendor_name, invoice_total, invoice_date
FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id
WHERE invoice_total = (
    SELECT MAX(invoice_total) FROM invoices
    WHERE vendor_id = v.vendor_id
)
ORDER BY invoice_total DESC;   -- 41 vs he got 36.

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...