Get Max date by another column

Question

I am trying to write a simple query to get the MAX DEMAND_DATE for each INV_CART_ID. Here is my existing query:

SELECT BUSINESS_UNIT, INV_CART_ID, INV_ITEM_ID, CART_COUNT_QTY, DEMAND_DATE
FROM PS_CART_CT_INF_INV A
WHERE A.INV_ITEM_ID = 1
AND A.BUSINESS_UNIT = '11MMS'
AND A.CART_COUNT_QTY <> 0
ORDER BY DEMAND_DATE DESC

Current Output:

Desired Output:

BUSINESS_UNIT  INV_CART_ID     INV_ITEM_ID     CART_COUNT_QTY    DEMAND_DATE
11MMS          405              1               5.0000           2018-05-29
11MMS          OUTPT_INFUSION   1               4.0000           2018-05-29
11MMS          938              1               15.0000          2018-05-31
11MMS          286              1               1.0000           2018-05-07
11MMS          708              1               4.0000           2018-04-05

This is what I have tried doing so far:

SELECT MAX(DEMAND_DATE) AS DEMAND_DATE, INV_CART_ID, BUSINESS_UNIT, 
 INV_ITEM_ID, CART_COUNT_QTY
FROM PS_CART_CT_INF_INV A
WHERE A.INV_ITEM_ID = 1
 AND A.BUSINESS_UNIT = '11MMS'
 AND A.CART_COUNT_QTY <> 0
 AND A.DEMAND_DATE IN (SELECT MAX (DEMAND_DATE) FROM PS_CART_CT_INF_INV B 
   WHERE A.INV_ITEM_ID = B.INV_ITEM_ID GROUP BY INV_CART_ID)
GROUP BY INV_CART_ID, BUSINESS_UNIT, INV_ITEM_ID, CART_COUNT_QTY

However it doesn't return all INV_CART_ID #'s and is not retrieving the correct row (wrong DEMAND_DATE):

Answer 1

Use ROW_NUMBER:

WITH cte AS (
    SELECT BUSINESS_UNIT, INV_CART_ID, INV_ITEM_ID, CART_COUNT_QTY, DEMAND_DATE,
        ROW_NUMBER() OVER (PARTITION BY INV_CART_ID ORDER BY DEMAND_DATE DESC) rn
    FROM PS_CART_CT_INF_INV
    WHERE
        INV_ITEM_ID = 1 AND
        BUSINESS_UNIT = '11MMS' AND
        CART_COUNT_QTY <> 0
)

SELECT
    BUSINESS_UNIT, INV_CART_ID, INV_ITEM_ID, CART_COUNT_QTY, DEMAND_DATE
FROM cte
WHERE rn = 1
ORDER BY DEMAND_DATE DESC;

If you don't want to use analytic functions, then I still would not use your current approach. Instead, I would join to a subquery, like this:

SELECT
    t1.BUSINESS_UNIT,
    t1.INV_CART_ID,
    t1.INV_ITEM_ID,
    t1.CART_COUNT_QTY,
    t1.DEMAND_DATE
FROM PS_CART_CT_INF_INV t1
INNER JOIN
(
    SELECT INV_CART_ID, MAX(DEMAND_DATE) AS MAX_DEMAND_DATE
    FROM PS_CART_CT_INF_INV
    WHERE INV_ITEM_ID = 1 AND BUSINESS_UNIT = '11MMS' AND CART_COUNT_QTY <> 0
    GROUP BY INV_CART_ID
) t2
    ON t1.INV_CART_ID = t2.INV_CART_ID AND t1.DEMAND_DATE = t2.MAX_DEMAND_DATE
WHERE
    t1.INV_ITEM_ID = 1 AND
    t1.BUSINESS_UNIT = '11MMS' AND
    t1.CART_COUNT_QTY <> 0;

The issue with your current query, even once corrected, is that it is using a correlated subquery in the WHERE clause. These are known to be potential performance killers, and so should be avoided if possible.

Answer 2

I think you want :

SELECT BUSINESS_UNIT, INV_CART_ID, INV_ITEM_ID, CART_COUNT_QTY, DEMAND_DATE
FROM PS_CART_CT_INF_INV AS a
WHERE INV_ITEM_ID = 1 AND BUSINESS_UNIT = '11MMS' AND 
      CART_COUNT_QTY <> 0 AND
      DEMAND_DATE = (SELECT MAX(b.DEMAND_DATE)
                     FROM PS_CART_CT_INF_INV as b
                     WHERE a.INV_CART_ID = b.INV_CART_ID
                    );

However, this would gives you duplicate records, if you want to avoid duplicates then you can use identity column or pk instead in WHERE clause :

. . .
WHERE pk = (SELECT TOP (1) b.pk
            FROM PS_CART_CT_INF_INV as b
            WHERE a.INV_CART_ID = b.INV_CART_ID
            ORDER BY b.DEMAND_DATE DESC
           );