db2 sql, how to get min and max value only in the same query with one table?

Question

with this query

SELECT distinct name,MIN(price) minprice, MAX(price) maxprice FROM cars where group='CNO' GROUP BY name HAVING MIN(price) > 1 order by minprice;

i obtain this result

NAME                                     MINPRICE    MAXPRICE   
---------------------------------------- ----------- -----------
Super CNO                                      20000       20000
CNO 340                                        40000       40000
CNO 110                                        60000       60000
CNO 790                                       100000      100000

How to get only the MIN and MAX price to see what car is most expensive? Something like this

NAME                                     MINPRICE    MAXPRICE   
---------------------------------------- ----------- -----------
Super CNO                                      20000       20000
CNO 790                                       100000      100000

of course we don't know the min and max price before the query so between 20000 and 100000 is not valid.

Answer 1

You can use window functions. Your results show min and max with the same value, which makes me suspect that is only one row per name. If so:

select *
from (
    select name, price,
        rank() over(order by price desc) rn_desc,
        rank() over(order by price) rn_asc
    from cars 
    where price > 1
) c
where 1 in (rn_asc, rn_desc)

If you really need aggregation:

select *
from (
    select name, min(price) min_price, max(price) max_price
        rank() over(order by max(price) desc) rn_desc,
        rank() over(order by min(price)) rn_asc
    from cars 
    group by name
    having min(price) > 1
) c
where 1 in (rn_asc, rn_desc)