Question 408096 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How do I find the min and max of a value of one column in postgresSQL and have it return only 2 rows?

I’m using postgresSQL and would like to find the minimum value and the maximum value in one column. I have to have a column titled Order ID, Minimum Order, Maximum Order in the query. See the below code for what I have come up with. I want to return only 2 rows. Currently it is returning 2000 rows which is the entirety of the table.

SELECT ord_id AS Order_ID
,min (order_tot) AS Minimum_Order
,max(order_tot) AS Maximum_Order
FROM orders
GROUP BY Order_ID
ORDER BY minimum_order;'
Tags:

2

Answers


  1. 0

    Note that if the order_tot are unique (unlikely, right?) then it is easy to get one Order_ID for the minimum and another for the maximum. However, assuming that the totals are not unique, you won’t just get two rows, although you can limit the number of rows.

    First, we cam look at a SELECT returning the minimum and maximum values:

    SELECT MIN(order_tot) AS min
  FROM orders;

SELECT MAX(order_tot) AS max
  FROM orders;

    These do not give you the corresponding order identifier, so we have to use a sub-query for that purpose:

    SELECT Order_ID,
       order_tot AS Minimum_Order
  FROM orders
 WHERE order_tot = (SELECT MIN(order_tot) FROM orders);

SELECT Order_ID,
       order_tot AS Maximum_Order
  FROM orders
 WHERE order_tot = (SELECT MAX(order_tot) FROM orders);

    You may use the LIMIT 1 in order to only grab the first order with that minimum and the first order with that maximum. Just know that with time that "first order" changes in PostgreSQL as you use UPDATE or DELETE+INSERT. To make sure you get the same order each time (until deleted), you would have to sort those orders, somehow. Maybe by Order_ID

    ... ORDER BY Order_ID LIMIT 1;

    If you really want both results in the a single SQL command, you can use the UNION keyword. That’s probably not that useful and makes the SQL more complicated than getting the minimum in one query and then the maximum in another. However, if you are creating a VIEW, it can useful to know about that keyword.

    So it would become something like this:

        SELECT Order_ID,
           order_tot AS Minimum_Order
      FROM orders
     WHERE order_tot = (SELECT MIN(order_tot) FROM orders);
  ORDER BY Order_ID
     LIMIT 1
UNION
    SELECT Order_ID,
           order_tot AS Maximum_Order
      FROM orders
     WHERE order_tot = (SELECT MAX(order_tot) FROM orders)
  ORDER BY Order_ID
     LIMIT 1;
    Login or Signup to reply.
  2. 0 
    with highest_order as(
    select od.order_total as Maximum_Order from orders od
    order by od.order_total desc
    limit 1
),
lowest_order as(
    select od.order_total as Minimum_Order from orders od
    order by od.order_total  
    limit 1
)
select * from highest_order
join lowest_order on true
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search