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.
我已经try 了max(Rn1),但它仍然返回所有12行.
一旦我可以获得1条记录的第一条和最后一条记录,我将把它扩展到所有的docnum.所以我不能使用"限制1".