How to retrieve the a single row in Sql

Question

There are different versions of si.version_number. e.g.

version_number=1
version_number=2
version_number=3

I need to retrieve the data having the maximum version number.

I know my query is somewhat wrong. Can anyone help me with this? Thanks.

    select si.point_id,si.ti_region_sur,si.phone_number,si.ti_service_instance_id,si.version_number 
    from 
    ti_invoice_account ia, ti_product_agreement pa, ti_asset_agreement aa, ti_service_instance si
    where
    si.version_number=max(si.version_number) and
    ia.ti_invoice_account_sur=pa.ti_invoice_account_sur and
    pa.ti_product_agreement_sur=aa.ti_product_agreement_sur and
    aa.ti_service_instance_id=si.ti_service_instance_id and
    ia.ti_invoice_account_sur=4897;

Please use 21st century join notation. It's been 20 years now since SQL:1999. — The Impaler, Apr 10 '19 at 20:53
Possible duplicate of [Get MAX value in mysql query](https://stackoverflow.com/questions/11913408/get-max-value-in-mysql-query) — ilkerkaran, Apr 10 '19 at 21:14

score 2 · Answer 1 · answered Apr 10 '19 at 20:58

In Oracle 12 you can use the ROW_NUMBER() window function, as in:

with 
x as (
  select
    si.point_id,
    si.ti_region_sur,
    si.phone_number,
    si.ti_service_instance_id,
    si.version_number,
    row_number() over (partition by si.point_id, si.ti_region_sur, 
      si.phone_number, si.ti_service_instance_id 
      order by si.version_number desc) as rn
  from ti_invoice_account ia
  join ti_product_agreement pa on ia.ti_invoice_account_sur = pa.ti_invoice_account_sur
                              and ia.ti_invoice_account_sur=pa.ti_invoice_account_sur
  join ti_asset_agreement aa on pa.ti_product_agreement_sur=aa.ti_product_agreement_sur
  join ti_service_instance si on aa.ti_service_instance_id=si.ti_service_instance_id
  where ia.ti_invoice_account_sur = 4897
)
select * from x where rn = 1

Note: I also converted your joins to modern join notation.

Thanks for this.. But I forgot to mention that its Oracle 11. will it work — Rosey Khatun, Apr 10 '19 at 21:03

How to retrieve the a single row in Sql

1 Answers1