How to extract the maximum and minimum values using the WHERE clause?

Question

I have written a script to extract the minimum and maximum value in the column (trail_seq_no). However, I do not know the maximum value in the column. Suppose I would like to extract all the columns in the table (using the select * ), how do I extract the maximum value in the column?

I can't use select max(trail_seq_no) because I want all the columns in the table.

select * from a_site_id a 
left join facility_trail_items_temp ft1 on ft1.a_site_id = a.a_site_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
left JOIN FACILITY_TRAIL_ITEMS_TEMP ft2 on ft2.parent_fac_trl_itm_id = ft1.parent_fac_trl_itm_id 
where ft2.resource_reference like 'ZG%' and ft2.trail_seq_no = 1.0;

Answer 1

use subquery in where condition

 select a.*  
    from a_site_id a 
    left join facility_trail_items_temp ft1 on ft1.a_site_id = a.a_site_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    _temp ft on ft.a_site_id = a.a_site_id
    left JOIN FACILITY_TRAIL_ITEMS_TEMP ft2 on ft2.parent_fac_trl_itm_id = ft1.parent_fac_trl_itm_id 
where 
ft2.resource_reference like 'ZG%' and
    (ft2.trail_seq_no = 1.0 or 
    ft2.trail_Seq_no = (select max(trail_seq_no) from  FACILITY_TRAIL_ITEMS_TEMP)
    )

Answer 2

You can use order by and limit to one row:

select *
from a_site_id a left join 
     facility_trail_items_temp ft1
     on ft1.a_site_id = a.a_site_id left JOIN 
     FACILITY_TRAIL_ITEMS_TEMP ft2
     on ft2.parent_fac_trl_itm_id = ft1.parent_fac_trl_itm_id 
where ft2.resource_reference like 'ZG%'
order by ft2.trail_seq_no desc
fetch first 1 row only;