Oracle sql fiding shipping delay

Question

can i know how i could write the sql statement in listing the shipping city and state for the order that has the longest shipping delay. I not sure need use the function Max in where

Answer 1

test table:

create table orders
(id int
,state varchar2(30)
,city varchar2(30)
,delay int);

test data:

insert into orders(id, state, city, delay)
select rownum as id, owner as state, table_name as city, abs(dbms_random.random) as delay
  from all_tables;

the query:

select * from (
select o.*, row_number() over (order by delay desc) pos from orders o
) where pos = 1

Answer 2

I'm assuming your data structure is flat, as in:

CREATE TABLE orders (
  ...
  shipping_city VARCHAR2(100),
  shipping_state VARCHAR2(100),
  shipping_delay NUMBER(7),
  ...
);

You can now write:

SELECT
  MAX(shipping_city)  KEEP (DENSE_RANK LAST ORDER BY shipping_delay),
  MAX(shipping_state) KEEP (DENSE_RANK LAST ORDER BY shipping_delay)
FROM
  orders