CODEWITHSUNDEEP
sqlpostgresql
Syed Ariff
Syed Ariff

Reputation: 732

Select max date before min date in another table

I have three tables.

Order_Status

Order_ID  order_status_id   Timestamp
1               2           12/24/19 0:00
1               3           12/24/19 0:10
1               4           12/24/19 0:30
1               5           12/24/19 1:00
2               2           12/24/19 15:00
2               3           12/24/19 15:07
2               9           12/24/19 15:10
2               8           12/24/19 15:33
2               10          12/24/19 16:00
4               4           12/24/19 19:00
4               2           12/24/19 19:30
4               3           12/24/19 19:32
4               4           12/24/19 19:40
4               5           12/24/19 19:45
5               2           1/28/19 19:30
5               6           1/28/19 19:48

Contact

Order_id    Contact_time
1           12/24/19 0:25
2           12/24/19 15:30
4           12/24/19 19:38
5           1/28/19 19:46

meta_status

order_status_id status_description
1               desc1
2               desc2
3               desc3
4               desc4
5               desc5

I am trying to retrieve the max order Timestamp before min Contact Time. I need it to be group by orderID, I also need the order_status_id and the status_description

This is my query so far

SELECT a.Order_ID,
       a.order_status_id,
       c.status_description,
       MAX(CASE
             WHEN a.order_timestamp < b.Contact_Time then
              a.order_timestamp
             ELSE
              null
           END) AS beforeContact
  FROM Order_Status a
  LEFT JOIN Contact b
    ON b.Order_ID = a.Order_ID
  LEFT JOIN meta_status c
    ON c.order_status_id = a.order_status_id
 GROUP BY a.Order_ID, a.order_status_id, c.status_description

But it still returns every row in the tables. I need it to be only 4 rows which represent 4 orders 1,2,4,5 and the max order timestamp before contact time.

Do I need to use subquery or windowing function for this?

Upvotes: 1

Views: 63

Answers (1)

Nikhil
Nikhil

Reputation: 3950

This is it:

select a.* from (SELECT a.ordertimestamp,
       a.order_status_id,
       c.status_description,
       a.order_timestamp AS beforeContact,
       rank() over (partition by a.Order_ID order by a.order_timestamp desc) as 
  rank1 
  FROM Order_Status a
  LEFT JOIN Contact b
    ON b.Order_ID = a.Order_ID
  LEFT JOIN meta_status c
    ON c.order_status_id = a.order_status_id
where a.order_timestamp < b.Contact_Time
 GROUP BY a.Order_ID, a.order_status_id, c.status_description)as a
where rank1=1;

Upvotes: 1

Related Questions