Find max sID without subquery

Question

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

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!

Answer 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>