CODEWITHSUNDEEP
mysqlsqldatabaseoracle-database
newbeginnner
newbeginnner

Reputation: 55

How to get min(date), max(date) and its quantity from the same table?

I am new in this sql coding, I am trying to learn something new. Hence I create a delivery table with a few attribute like item code, delivery date, delivery quantity. So how to get first delivery date (min date) and the quantity delivered and also latest delivery date (max date) and its quantity delivered from the same table?

I tried to use union clause to get the min (date) and max (date) with its quantity. But the output doesn't display like what I imagined

Here is what I tried

SELECT item_code , MAX(date) AS "Latest_delivery_date", quantity
FROM delivery
WHERE item_code='110192'
GROUP BY item_code
union all
SELECT item_code , MIN(date) AS "First_delivery_date", quantity
FROM delivery 
WHERE item_code='110192'
GROUP BY item_code;

I want the output to look something like

example expected output

Upvotes: 1

Views: 873

Answers (2)

Nick
Nick

Reputation: 147146

One way to solve this is to make a derived table of the MIN and MAX delivery dates for an item and then JOIN that back to the table to find the quantities for those dates. For example:

SELECT m.item_code, 
       m.min_date AS "First_delivery_date",  d1.quantity AS "First_quantity",
       m.max_date AS "Latest_delivery_date", d2.quantity AS "Latest_quantity"
FROM (SELECT item_code, MAX(date) AS max_date, MIN(date) AS min_date
      FROM delivery
      GROUP BY item_code) m
JOIN delivery d1 ON d1.item_code = m.item_code AND d1.date = m.min_date
JOIN delivery d2 ON d2.item_code = m.item_code AND d2.date = m.max_date

Demo on dbfiddle

To select results for one particular item_code, add a WHERE clause to the derived table. That will restrict the results of that table to only the row for that item_code, making the subsequent JOINs more efficient. For example:

SELECT m.item_code, 
       m.min_date AS "First_delivery_date",  d1.quantity AS "First_quantity",
       m.max_date AS "Latest_delivery_date", d2.quantity AS "Latest_quantity"
FROM (SELECT item_code, MAX(date) AS max_date, MIN(date) AS min_date
      FROM delivery
      WHERE item_code = '010997'
      GROUP BY item_code) m
JOIN delivery d1 ON d1.item_code = m.item_code AND d1.date = m.min_date
JOIN delivery d2 ON d2.item_code = m.item_code AND d2.date = m.max_date

Upvotes: 2

Popeye
Popeye

Reputation: 35900

You can use analytical functions as the following:

SQL> CREATE TABLE DELIVERY (
  2      ITEM_CODE   NUMBER,
  3      DATE_       DATE,
  4      QUANTITY    NUMBER
  5  );

Table created.

SQL>
SQL> INSERT INTO DELIVERY VALUES (
  2      110192,
  3      DATE '2019-05-05',
  4      30
  5  );

1 row created.

SQL>
SQL> INSERT INTO DELIVERY VALUES (
  2      110192,
  3      DATE '2019-01-01',
  4      10
  5  );

1 row created.

SQL>
SQL> INSERT INTO DELIVERY VALUES (
  2      110192,
  3      DATE '2019-10-10',
  4      25
  5  );

1 row created.

SQL>
SQL> INSERT INTO DELIVERY VALUES (
  2      110192,
  3      DATE '2019-08-08',
  4      19
  5  );

1 row created.

SQL>
SQL> SELECT DISTINCT
  2      ITEM_CODE,
  3      MIN(DATE_) OVER(
  4          PARTITION BY ITEM_CODE
  5      ) AS FIRST_DELIVERY_DATE,
  6      MAX(QUANTITY) KEEP(DENSE_RANK FIRST ORDER BY DATE_) OVER(
  7          PARTITION BY ITEM_CODE
  8      ) AS FIRST_QUANTITY,
  9      MAX(DATE_) OVER(
 10          PARTITION BY ITEM_CODE
 11      ) AS LATEST_DELIVERY_DATE,
 12      MAX(QUANTITY) KEEP(DENSE_RANK FIRST ORDER BY DATE_ DESC NULLS LAST) OVER(
 13          PARTITION BY ITEM_CODE
 14      ) AS LATEST_QUANTITY
 15  FROM
 16      DELIVERY;

 ITEM_CODE FIRST_DEL FIRST_QUANTITY LATEST_DE LATEST_QUANTITY
---------- --------- -------------- --------- ---------------
    110192 01-JAN-19             10 10-OCT-19              25

SQL>

Cheers!!

Upvotes: 1

Related Questions