Search code examples
sqlapache-sparkmaxmin

return the Min and Max()row_number that is found spark SQL

select distinct 
snap1.rn,
snap2.rn1,
snap1.doc_num


from (select DISTINCT *,
row_number() over (partition by car_id order by SNAPSHOT_DATE, CAR_ID) rn
from MY_TABLE
)snap1 


left join 
(select DISTINCT *,
row_number() over (partition by car_id order by SNAPSHOT_DATE, CAR_ID) rn1
from MY_TABLE
)snap2 
on snap1.CAR_ID = snap2.CAR_ID

where snap1.DOC_NUM = '73927243'
and snap1.rn = '1'

I want to retrieve 1 record with the 1st and last record, e.g. RN = 1 and RN1 = 12.
not the others records. note: the max is not always 12, could be 10, 11 etc.

have tried max(rn1) but its still returning all 12 rows.

once I can get the 1st and last for 1 record I will expand it to all doc_num. so i can't use 'Limit 1'.

RESULT NEEDED

Solution

  • You could select the first row sorted in reverse order:

    select  *
from    (
        select  row_number() over (partition by car_id
                        order by snapshot_date) rn
        ,       *
        from    MY_TABLE
        ) tmin
left join
        (
        select  row_number() over (partition by car_id
                        order by snapshot_date DESC) rn
        ,       *
        from    MY_TABLE
        ) tmax
on      tmin.car_id = tmax.car_id
        and tmax.rn = 1
where   tmin.rn = 1