CODEWITHSUNDEEP
mysqlsql
Daveh0
Daveh0

Reputation: 1012

mySQL - query to fetch first 2 records with unique values for specific column

I have an app that records and processes product orders. From the time the order is initially created to when it is finally submitted for fulfillment, 1 or more of its properties may be changed 1 or more times by 1 or more employees along the way. I'm storing the Order Numbers and the Employees who started the orders in 1 table and then all changes to all the orders (including starting details) in another table. Something like this:

Orders

ORDER_ID   EMPLOYEE
1          dan
2          dan
3          fred

Order_Activity

ACTIVITY_ID    ORDER_ID    UPDATER    DATE      QTY    SIZE    COLOR    STATUS
1               1          dan        1/1/2017  5      m       red      new
2               1          dan        1/3/2017  15     m       red      updated
3               1          tom        1/9/2017  15     l       red      submitted
4               2          dan        1/1/2017  25     l       blue     new
5               2          tom        1/1/2017  25     l       blue     submitted
6               3          fred       1/9/2017  15     s       red      new
7               3          fred       1/10/2017 5      l       red      updated
8               3          fred       1/11/2017 5      m        red     updated
9               3          tom        1/11/2017 5      m        red     submitted

I need to display the 2 most recent orders with the employee who initiated the order as well as the orders' properties (qty, size and color) as they were when the status was changed to 'submitted'.

If I only needed ONE order (the most recent), I think I could use:

SELECT 
    Orders.EMPLOYEE, 
    Order_Activity.QTY, 
    Order_Activity.SIZE,
    Order_Activity.COLOR FROM Orders 
        INNER JOIN Order_Activity 
            ON Orders.ORDER_ID = Order_Activity.ORDER_ID 
            ORDER BY Order_Activity.date DESC LIMIT 1

However I am having a hard time envisioning how to find the next record with a different value for ORDER_ID while keeping it all in one query.

Can anyone help me with a query to produce something like this (based on the data provided above):

ORDER_ID    EMPLOYEE    QTY    SIZE    COLOR
3           fred        5      m       red
1           dan         15     l       red

or can't it even be done in 1 query?

Upvotes: 1

Views: 66

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a inner join with the subquery for max date group da order_id

select o.ORDER_ID, o.EMPLOYEE , oa.QTY, oa.SIZE, oa.SIZE
from Orders o 
inner join  Order_Activity oa on o.ORDER_ID = oa.ORDER_ID 
inner join  (
  select  ORDER_ID, max(date) max_date
  from Order_Activity
  group by ORDER_ID 
) tt on tt.ORDER_ID = o.ORDER_ID 
    and tt.max_date = oa.date

Upvotes: 1

mchist
mchist

Reputation: 132

If you would have MS SQL then I should have to advise you the next query: 

WITH prepare AS
(
SELECT 
     Orders.EMPLOYEE
    , Order_Activity.QTY
    , Order_Activity.SIZE
    , Order_Activity.COLOR
    , ROW_NUMBER() OVER (PARTITION BY Orders.ORDER_ID ORDER BY Order_Activity."date") AS rnbr
FROM Orders 
        INNER JOIN Order_Activity 
            ON Orders.ORDER_ID = Order_Activity.ORDER_ID 
)
SELECT

prepare.EMPLOYEE
, prepare.QTY
, prepare.SIZE
, prepare.COLOR

FROM prepare
WHERE prepare.nmbr = 1

You may try this statement in My SQL because this query conducts to a Standart SQL.

Upvotes: 0

Related Questions