-1

I have a "sSamples" table which contains UniqueID, sDate and sID-s like:

enter image description here

I need the SID-s by the latest sDate and grouped by UniqueID.

Result should look like:

UniqueID  |  SID
---------------------
031311579 | 11641325
053600109 | 11641353
066051428 | 11641379
093468114 | 11641350

I tried with different query versions but the perfomance was very slow. What is the best solution for this?

Thanks for the answers!

MT0
  • 143,790
  • 11
  • 59
  • 117
Steve88
  • 2,366
  • 3
  • 24
  • 43
  • Duplicate of https://stackoverflow.com/q/121387/1509264 or https://stackoverflow.com/q/3491329/1509264 or https://stackoverflow.com/q/16529701/1509264 or https://stackoverflow.com/q/10342405/1509264 (or many others). – MT0 Jan 20 '21 at 13:49

2 Answers2

1

You didn't post what your versions were (so that we wouldn't repost what you already tried). Anyway, if you didn't try analytic functions, here's how (sample data from lines #1 - 5; query you might want to try starts at line #6):

SQL> with ssamples (uniqueid, sdate, sid) as
  2    (select '031311579', to_date('18.01.2021 08:18', 'dd.mm.yyyy hh24:mi'), 11641325 from dual union all
  3     select '066051428', to_date('20.01.2021 07:50', 'dd.mm.yyyy hh24:mi'), 11641381 from dual union all
  4     select '066051428', to_date('20.01.2021 07:51', 'dd.mm.yyyy hh24:mi'), 11641379 from dual
  5    )
  6  select uniqueid, sid
  7  from (select uniqueid, sdate, sid, row_number() over (partition by uniqueid order by sdate desc) rn
  8        from ssamples
  9       )
 10  where rn = 1;

UNIQUEID         SID
--------- ----------
031311579   11641325
066051428   11641379

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
1

Looking at the expected output, It seems you can use max with keep clause as follows

Select uniqueid, max(sid) keep (dense_rank last order by sdate) as sid
 From your_table
Group by uniqueid
Popeye
  • 35,427
  • 4
  • 10
  • 31