sql max date query

Question

This is my query:

SELECT ITM.CODE
     , Price
     , STL.DATE_ AS gun
FROM LG_094_ITEMS ITM WITH (NOLOCK)
INNER JOIN LG_094_01_STLINE STL ON ITM.LOGICALREF = STL.STOCKREF
WHERE Price > 0
    AND TRCODE = 1
    AND CANCELLED = 0
    AND INVOICEREF > 0
    AND STL.LINETYPE = 0

I couldn't figure our how to get the latest prices of the codes.

Thanks for your help.

We can't figure it out either unless you post your table structure and sample data.... — WillardSolutions
– WillardSolutions, Commented Apr 25, 2016 at 13:02

Devart · Accepted Answer · 2016-04-25 13:04:45Z

3

You can use ROW_NUMBER() here:

SELECT *
FROM (
    SELECT ITM.CODE
         , Price
         , STL.DATE_ AS gun
         , ROW_NUMBER() OVER (PARTITION BY ITM.CODE ORDER BY STL.DATE_ DESC) AS RN
    FROM LG_094_ITEMS ITM WITH (NOLOCK)
    INNER JOIN LG_094_01_STLINE STL ON ITM.LOGICALREF = STL.STOCKREF
    WHERE Price > 0
        AND TRCODE = 1
        AND CANCELLED = 0
        AND INVOICEREF > 0
        AND STL.LINETYPE = 0
) prices
WHERE RN = 1

edited Apr 25, 2016 at 13:04

Devart

120k23 gold badges171 silver badges189 bronze badges

answered Apr 25, 2016 at 13:03

Haytem BrB

1,5093 gold badges16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Devart · Accepted Answer · 2016-04-25 13:03:16Z

2

SELECT *
FROM (
    SELECT ITM.CODE
         , Price
         , STL.DATE_ AS gun
         , RowNum = ROW_NUMBER() OVER (PARTITION BY ITM.CODE ORDER BY STL.DATE_ DESC)
    FROM LG_094_ITEMS ITM
    INNER JOIN LG_094_01_STLINE STL ON ITM.LOGICALREF = STL.STOCKREF
    WHERE Price > 0
        AND TRCODE = 1
        AND CANCELLED = 0
        AND INVOICEREF > 0
        AND STL.LINETYPE = 0
) t
WHERE t.RowNum = 1

answered Apr 25, 2016 at 13:03

Devart

120k23 gold badges171 silver badges189 bronze badges

Collectives™ on Stack Overflow

sql max date query

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related