Hi, I've two tables:
Product:
ProductID
ProductName
StateLog:
StateLogID
ProductID (Foreign Key)
State (bit)
TimeStramp (DateTime)
I need to find the heighest StateLog.TimeStamp for each StateLog.ProductID there have the StateLog.State = 0
views:
34answers:
2
A:
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
Joel Coehoorn
2010-05-24 19:51:54
Hi, thx for your answer. What is ProductTimeStramps? I've never seen a 'WITH' before. It looks interesting and I want to run your solution also. Sorry if it's obvious.
radbyx
2010-05-24 20:18:59
It's called a Common Table Expression (CTE). Supported by Oracle and Sql Server 2005 or later, but not mysql. You could easily just write it as an uncorrelated derived table (subquery) instead.
Joel Coehoorn
2010-05-24 20:22:18
Thanks Joel, I wish I could give you some points to :)
radbyx
2010-05-24 20:48:44
+2
A:
try:
SELECT
p.ProductID, p.ProductName, dt.MaxTimeStamp
FROM Product p
LEFT OUTER JOIN (SELECT
ProductID, MAX(TimeStramp) 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(TimeStramp) AS MaxTimeStamp
FROM StateLog
WHERE State = 0
GROUP BY ProductID
KM
2010-05-24 19:52:21