CODEWITHSUNDEEP
sql
radbyx
radbyx

Reputation: 9670

How do I find the latest DateTime for each of my products in combination with another condition

Product:
ProductID
ProductName

StateLog:
StateLogID
ProductID (Foreign Key)
State (bit)
TimeStamp (DateTime)

I need to find the highest StateLog.TimeStamp for each StateLog.ProductID there have the StateLog.State = 0

Upvotes: 1

Views: 3951

Answers (2)

KM.
KM.

Reputation: 103667

try:

SELECT
    p.ProductID, p.ProductName, dt.MaxTimeStamp
    FROM Product p
        LEFT OUTER JOIN (SELECT
                            ProductID, MAX(TimeStamp) AS MaxTimeStamp
                            FROM StateLog
                            WHERE State = 0
                            GROUP BY ProductID
                        ) dt ON p.ProductID =dt.ProductID 
    ORDER BY p.ProductName

or just:

SELECT
   ProductID, MAX(TimeStamp) AS MaxTimeStamp
   FROM StateLog
   WHERE State = 0
   GROUP BY ProductID

Upvotes: 3

Joel Coehoorn
Joel Coehoorn

Reputation: 416081

WITH ProductTimeStamps AS
(
   SELECT ProductID, Max(TimeStamp) FROM SateLog WHERE State=0 GROUP BY ProductID
)
SELECT sl.StateLogID, sl.ProductID, sl.TimeStamp
FROM StateLog sl
INNER JOIN ProductTimeStamps pts ON pts.ProductID = sl.ProductID AND pts.TimeStamp = sl.TimeStamp
WHERE sl.State = 0

Upvotes: 1

Related Questions