HOW DO I SELECT EARLIEST ROW

Question

I have two tables: orders and dates. Like this:

id	order_num
1	101
2	102
3	103

order_id	log_date	user_name
1	27-may-22	Anthony
1	16-apr-22	Paul
1	19-may-22	Daniel
2	02-feb-22	Chris
2	18-mar-22	Jenny
2	12-feb-22	Sonya
3	06-jun-22	Daniel
3	07-jun-22	Jacob

I need to select order_num with the username who logged order first (log_date). Like this:

order_num	username
101	Paul
102	Chris
103	Daniel

I tried something like this:

with name as (
  select order_id, min(log_date) as log_date
  from dates
 group by order_id)

select d.user_name,
       o.order_num
from orders o 
inner join name n on o.id = n.order_id
inner join dates d on d.order_id = n.order_id;

But it selects all usernames assigned to order_num. Any help will be appreciated.

Answer 1

Here are a few options for sample data you posted:

SQL> with
  2  orders (id, order_num) as
  3    (select 1, 101 from dual union all
  4     select 2, 102 from dual union all
  5     select 3, 103 from dual
  6    ),
  7  dates (order_id, log_date, user_name) as
  8    (select 1, date '2022-05-27', 'Anthony' from dual union all
  9     select 1, date '2022-04-16', 'Paul'    from dual union all
 10     select 1, date '2022-05-19', 'Daniel'  from dual union all
 11     select 2, date '2022-02-02', 'Chris'   from dual union all
 12     select 2, date '2022-03-18', 'Jenny'   from dual union all
 13     select 2, date '2022-02-12', 'Sonya'   from dual union all
 14     select 3, date '2022-06-06', 'Daniel'  from dual union all
 15     select 3, date '2022-06-07', 'Jacob'   from dual
 16    )

Oldfashioned way scans the dates table twice (once to find the first log date in a subquery, and to join it with the orders table) so it isn't very efficient for large data sets.

 17  select o.order_num, d.user_name
 18  from orders o join dates d on d.order_id = o.id
 19  where d.log_date = (select min(d1.log_date)
 20                      from dates d1
 21                      where d1.order_id = d.order_id
 22                     )
 23  order by o.order_num;

 ORDER_NUM USER_NAME
---------- ----------
       101 Paul
       102 Chris
       103 Daniel

SQL>

Another option uses a CTE with the row_number analytic function to "sort" rows by log date per each order ID, and then joins result of that CTE with orders table to get the final result, filtering only rows that ranked as the highest (use rank function instead if there's a chance that two or more users log in on the same date):

 17  temp as
 18    (select d.order_id, d.user_name,
 19       row_number() over (partition by d.order_id order by d.log_date) rn
 20     from dates d
 21    )
 22  select o.order_num, t.user_name
 23  from orders o join temp t on t.order_id = o.id
 24  where t.rn = 1
 25  order by o.order_num;

 ORDER_NUM USER_NAME
---------- ----------
       101 Paul
       102 Chris
       103 Daniel

SQL>

Or, you could use first_value analytic function which is similar to previous option; as select itself results in as many rows as there are in joined data set (join between orders and dates), use the distinct keyword to remove duplicates:

 17  select distinct o.order_num,
 18    first_value(d.user_name) over (partition by order_id order by log_date) user_name
 19  from orders o join dates d on d.order_id = o.id
 20  order by o.order_num;

 ORDER_NUM USER_NAME
---------- ----------
       101 Paul
       102 Chris
       103 Daniel

SQL>

Answer 2

Your query woud have worked, but you forgot and d.log_date = n.log_date in the dates join.

However, when there is only one first user per order, this gets much simpler in Oracle: just join the tables and use Oracle's proprietary KEEP FIRST aggregation function:

select o.order_num, max(d.user_name) keep (dense_rank first order by d.log_date)
from orders o
join dates d on d.order_id = o.id
group by o.order_num
order by o.order_num;