'Group By but not wanting to group one column
I have a set of data. Example below:
I only want the most recent permit number. After I get the most recent permit number I want to find all of those permits that do not belong in New York. (document_region).
How can I do that without joining the same table twice?
Query Example:
WITH cohort AS ( -- Gives me all the drivers actively driving around in New York right now
SELECT
distinct driver_id
,MAX(dropped_off) AS last_ride_timestamp
FROM
TABLE A
WHERE
1 = 1
AND region = 'New York'
GROUP BY 1
)
, docs as ( -- all relevant documentation information
SELECT
driver_id
-- , document_region -- if i group by document_region i'll get an older document
, MAX_BY(permit_number, expiration_date)
,MAX_BY(status, last_updated_at) as most_recent_status
,MAX(expiration_date) as expiration_date
FROM TABLE B
WHERE document_type = 'DriverLicense'
GROUP BY 1
)
, docs_2 as ( -- same doc table but now I want the region column
SELECT
driver_id
,document_region
FROM TABLE B
WHERE document_type = 'DriverLicense'
)
, stage as (
SELECT *
FROM
cohort c
LEFT JOIN docs d ON c.driver_id = d.driver_id
LEFT JOIN docs_2 d2 on d2.driver_id = d.driver_id
)
select *
from stage
where document_subtype != 'New York'
Result:
|driver_id|document_region|most_recent_status|permit_number|expiration_date| :--|:--|:--|:--|--:|
|82313123|CALIFORNIA|approved|A2345|2018-01-11 0:00:00|
|82313123|NEW YORK|approved|B32483|2022-10-13 0:00:00|
Desired result: Just the line with the CALIFORNIA result.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
