CODEWITHSUNDEEP
sqldb2
Bob
Bob

Reputation: 885

How to use MAX with additional criteria

A) When goods are sold, a table will record an “O” in column ID & a “N” in column TYPE:-

SKU      ID  TYPE  NR
-------------------------
HAB1122  O   N     201211

B) When goods are returned, a table will record an “O” in column ID & a “R” in column TYPE:-

SKU      ID  TYPE  NR
-------------------------
HAB1122  O   R     201223

Scenario 1

Goods were sold & returned later. It was sold subsequently:-

SKU      ID  TYPE  NR
-------------------------
HAB1122  O   N     201211
HAB1122  O   R     201223
HAB1122  O   N     201245
HAB2233  O   N     201263
HAB3344  O   N     201303
HAB3344  O   R     201341

Using SQL:-

Select SKU, max(NR) 
from tableA
where SKU like ‘HAB%’ and ID = ‘O’
group by SKU

Results:-

SKU       NR
----------------
HAB1122   201245
HAB2233   201263
HAB3344   201341

This is the correct result as I wish to track down the last NR of the goods sold whether the goods are sold or returned.

If I wish to track down the last NR of goods with status sold only, the expected results should be:-

SKU      NR
---------------
HAB1122  201245
HAB2233  201263

HAB3344 is not considered as the goods are returned & not sold subsequently.

How should I change the SQL?

Upvotes: 0

Views: 550

Answers (4)

J Cooper
J Cooper

Reputation: 5008

SELECT *
FROM tableA AS T1
WHERE SKU like ‘HAB%’ and ID = ‘O’ AND T1.[TYPE] = 'N'
AND NOT EXISTS (
    SELECT *
    FROM tableA AS T2
    WHERE T2.SKU = T1.SKU
    AND T2.NR > T1.NR
    AND ID = ‘O’
)

Results:

SKU        ID   TYPE NR
---------- ---- ---- -----------
HAB1122    O    N    201245
HAB2233    O    N    201263

From a quick google search, DB2 appears to support Window Functions and CTE's. I can say that for at least SQL Server, window functions perform better in this scenario:

ROW_NUMBER(): http://forums.devshed.com/db2-development-114/rownumber-over-169453.html

CTE's: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/samples/clp/s-cte-db2.htm

Window Functions Approach:

WITH T AS (
    SELECT T1.*,
           ROW_NUMBER() OVER(PARTITION BY SKU ORDER BY NR DESC) AS RN
    FROM tableA AS T1 
    WHERE ID = ‘O’
)
SELECT T.SKU, T.NR
FROM T 
WHERE RN = 1 AND SKU like ‘HAB%’ AND T1.[TYPE] = 'N';

Upvotes: 1

Andriy M
Andriy M

Reputation: 77697

If your database system supports ranking functions, like ROW_NUMBER(), you could try the following:

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY NR DESC) AS RNK
  FROM atable
  WHERE SKU LIKE 'HAB%'
    AND ID = 'O'
)
SELECT
  SKU,
  NR
FROM ranked
WHERE TYPE = 'N'
  AND RNK = 1

Upvotes: 0

John Doyle
John Doyle

Reputation: 7793

Try this:

select   SKU, max(NR)
from     TABLEA T1
where    SKU like 'HAB%' and ID = 'O'
group by SKU
having   max(NR) <> (select nvl(max(NR), 0)
                     from   TABLEA T2
                     where  T1.SKU = T2.SKU and T2.type = 'R');

This is using Oracle. You didn't indicate your system so the having may not work specifically on yours but an equivalent will.

Upvotes: 2

penartur
penartur

Reputation: 9912

Select SKU, max(NR) from tableA where SKU like 'HAB%' and ID = 'O' and TYPE = 'N' group by SKU?

Upvotes: 0

Related Questions