Search code examples
sqloracle-databasetop-n

Find the most expensive order

I'm trying to find the most expensive order within my table, and I've achieved this, but I'd like to know how to just return this one particular row. Right now it turns all rows with the most expensive order at the top. I'm not quite sure how to return just the most expensive order. I've grouped the orders by the order number (order_numb). I've tried using IN and a self join but I can't seem to get it to work.

My table

enter image description here

My current query that returns the correct result, just it returns all rows

SELECT order_numb, sum(cost_each) as totalSum
FROM order_lines 
GROUP BY order_numb
ORDER BY totalSum DESC

What I'm trying to achieve

I'm trying to retrieve the most expensive order by itself.

I'm using Oracle as my database.

Solution

  • Use ROWNUM to filter the first row (most expensive order) of the result set:

    SELECT t.*
FROM (
  SELECT order_numb, sum(cost_each) as totalSum 
  FROM order_lines 
  GROUP BY order_numb 
  ORDER BY totalSum DESC
) t
WHERE ROWNUM <= 1