CODEWITHSUNDEEP
sql-server
Jackal
Jackal

Reputation: 3521

SQL Get supplier by latest date

Hi I have a table where it has supplier, product and the date,

Right now my query returns suppliers, products and the latest date for each

    SELECT 
        Supplier,
        Product,
        MAX(LastDate)
    FROM 
        Products
    WHERE Supplier IN (''J1006962'', ''00000820'') 
    AND Product = ''M131200''       
    GROUP BY 
        Supplier,
        Product

this returns 

Supplier     Product    LastDate
00000820     M131200    1190506
J1006962     M131200    1180516

the outcome should be 

Supplier     Product    LastDate
00000820     M131200    1190506

the date is in db2 format since it's a linked server

Upvotes: 0

Views: 353

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

If you only have 1 product then dont need GROUP BY

SELECT TOP 1
    Supplier,
    Product,
    LastDate
FROM 
    Products
WHERE Supplier IN ('J1006962', '00000820') 
AND Product = ''M131200''       
ORDER BY LastDate DESC;

If want the last supplier for each product use ROW_NUMBER()

SELECT *
FROM (
    SELECT 
        Supplier,
        Product,
        LastDate,
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY LastDate DESC) as rn
    FROM 
        Products
    WHERE Supplier IN ('J1006962', '00000820') 
)
WHERE rn = 1

Upvotes: 1

dbajtr
dbajtr

Reputation: 2044

Just add a SELECT TOP 1 and order it:

SELECT TOP 1
    Supplier,
    Product,
    MAX(LastDate)
FROM 
    Products
WHERE Supplier IN (''J1006962'', ''00000820'') 
AND Product = ''M131200''       
GROUP BY 
    Supplier,
    Product
ORDER BY LastDate DESC

Upvotes: 1

AdrianIT
AdrianIT

Reputation: 57

If I understand correctly you want to do this:

SELECT 
    Supplier,
    Product,
    MAX(LastDate)
FROM 
    Products
WHERE Supplier IN (''J1006962'', ''00000820'') 
AND Product = ''M131200''       
GROUP BY 
    Supplier,
    Product
ORDER BY(LastDate);

Upvotes: 1

Related Questions