'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