Question

DATABASE SQL

Task 4.1.2. Find the earliest po_date for each vendor_id in the pos table, include all the columns from the pos table and order by vendor_id. This cannot be done by a GROUP BY. You will need to use a correlated query based on the vendor_id.

For example:

Result
job_id po_id po_date vendor_id ---------- ---------- ---------- ---------- 005 FFF 1989-12-01 ABC 004 CCC 1990-01-05 SOS 006 GGG 1988-07-15 XYZ

table

job_id po_id po_date vendor_id
---------- ---------- ---------- ----------
002 AAA 1990-05-20 ABC
002 BBB 1990-03-15 XYZ
004 CCC 1990-01-05 SOS
004 DDD 1990-01-01 ABC
005 EEE 1990-01-15 SOS
005 FFF 1989-12-01 ABC
006 GGG 1988-07-15 XYZ

cannot use group by clause!

Answer 1

NOTE : GROUP BY clause is aggregate while the PARTITION BY clause is analytic

WITH correlatedResult AS
(
SELECT *
           ,ROW_NUMBER()OVER(PARTITION BY vendor_id ORDER BY po_date DESC ) as row_num,
   FROM pos
)
SELECT job_id,po_id,po_date,vendor_id
FROM correlatedResult
WHERE row_num = 1;