How select other columns from Oracle 11G query?

Question

I have this query which groups the results by ORDER#.

SELECT ORDER#, MAX(SHIPDATE - ORDERDATE) DELAYDAYS FROM ORDERS GROUP BY ORDER#;

My goal is to return the single ORDER# that has the longest shipping delay. Not the entire set. How can I accomplish this?

Tried this too. Does not work. This gives me the single column, but not the ORDER#.

SELECT MAX(X.DELAYDAYS) FROM
   (SELECT ORDER#, MAX(SHIPDATE - ORDERDATE) DELAYDAYS 
    FROM ORDERS GROUP BY ORDER#) X;

score 1 · Answer 1 · answered Jun 30 '12 at 00:02

1

Maybe this?

SELECT X.ORDER# 
FROM (
  SELECT ORDER#, MAX(SHIPDATE - ORDERDATE) DELAYDAYS 
  FROM ORDERS 
  GROUP BY ORDER#
  ORDER BY DELAYDAYS DESC
  ) X
WHERE ROWNUM = 1;

This will not give a consistent result if there is a tie in DELAYDAYS though.

answered Jun 30 '12 at 00:02

andy holaday

2,282
1
17
25

You can make the answer determinant (consistent) by extending the ORDER BY: `ORDER BY DELAYDAYS DESC, ORDER#` – dbenham Jun 30 '12 at 05:38
There is a lot of conflicting information out there. Some claim you should never use ORDER BY within an Oracle subquery: http://stackoverflow.com/questions/5119190/oracle-sql-order-by-in-subquery-problems. Others claim it is OK since Oracle 8i release 1.1, as long as you use it correctly: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:495221712170. It definitely did not work prior to 8i, but it looks to be OK now. – dbenham Jun 30 '12 at 05:45

dbenham · Accepted Answer · 2012-06-30T02:08:35.387

To get a list of all orders that tie for the longest delay:

with order_delay as (
  select order#,
           max( shipdate - orderdate ) as delayDays
      from orders
     group by order#
),
ranked_order_delay as (
  select order#,
         delayDays,
         rank() over( order by delayDays desc ) as delayRank
    from order_delay
)
select order#, delayDays from ranked_order_delay
 where delayRank=1
;

Use the query below to get a single order, taking the lowest order# in case of a tie for the longest delay. (should always strive for a determinant result) I believe this is the optimal solution, requiring only one pass through the data.

select min(order#) keep (dense_rank last order by (shipdate-orderdate)) as order#,
       max(shipdate-orderdate) delaydays
  from orders

To take the highest order# in case of a tie for longest delay, then simply use max(order#) instead.

Edit - I knew there was a better way. It just took a wile. I was hung up thinking I had to determine the max delay for each order#, but then I realized it wasn't necessary for this query.

How select other columns from Oracle 11G query?

2 Answers2