Find the first order of a supplier in a day using SQL-CodePudding

I am trying to write a query to return supplier ID (sup_id), order date and the order ID of the first order (based on earliest time).

 -------- -------- ------------ -------- ----------------- 
|orderid | sup_id |    items   | sales  |   order_ts      |
 -------- -------- ------------ -------- ----------------- 
|1111132 |  3     |     1      |  27,0  |  24/04/17 13:00 |   
|1111137 |  3     |     2      |  69,0  |  02/02/17 16:30 |     
|1111147 |  1     |     1      |  87,0  |  25/04/17 08:25 |    
|1111153 |  1     |     3      |  82,0  |  05/11/17 10:30 |   
|1111155 |  2     |     1      |  29,0  |  03/07/17 02:30 |   
|1111160 |  2     |     2      |  44,0  |  30/01/17 20:45 |   
|....... | ...    |      ...   |  ...   |     ...    ...  |   
 -------- -------- ------------ -------- -----------------

Output I am looking for:

 -------- -------- ------------ 
| sup_id |  date  |  order_id  | 
 -------- -------- ------------ 
|....... | ...    |      ...   |  
 -------- -------- ------------

I tried using a subquery in the join clause as below but didn't know how to join it without having selected order_id.

SELECT sup_id, date(order_ts), order_id
FROM sales s
JOIN
(
SELECT sup_id, date(order_ts) as date, min(time(order_date))
FROM sales
GROUP BY merchant_id, date
) m
on ...

Kindly assist.

CodePudding user response：

You can use not exists:

select *
from sales
where not exists (
    -- find sales for same supplier, earlier date, same day
    select *
    from sales as older
    where older.sup_id = sales.sup_id
    and older.order_ts < sales.order_ts
    and older.order_ts >= cast(sales.order_ts as date)
)

CodePudding user response：

The query below might not be the fastest in the world, but it should give you all information you need.

select order_id, sup_id, items, sales, order_ts
from sales s
where order_ts <= (
       select min(order_ts)
         from sales m
        where m.sup_id = s.sup_id
)

CodePudding user response：

select sup_id,  min(order_ts),  min(order_id) from sales  
where order_ts  = '2022-15-03'
group by sup_id

Assumed orderid is an identity / auto increment column