how to add column in SQL Query that incl. LEFT OUTER JOIN

Question

I have this Query:

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

It works like it should, but now I need to SELECT "State" out too.

The tricky part is, that I only want the lastest "TimeStamp" where "State" was false. But now I also need the "State" for the lastest "TimeStamp".

I tried this:

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

But it didn't work, because it gave me the "State" for the lastest "TimeStamp".

So I hope there is some clever heads out there that can help me. I'm guessing that this is either very simple or very hard to solve.

Answer 1

Great formatting work by tvanfosson and OMG Ponies.

When using GROUP BY every column needs to either:
1. Have an aggregate function applied to it, or
2. Appear in the GROUP BY clause.

I don't know what Status is, but I'm assuming you need it.
So this is an example of how your query should look:

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

But all of this is foolishness because, as mentioned in a comment, you filter by State = 0 so there is no possibility that your query will return anything other than a 0 for State.

Answer 2

Struggling to decipher what you're looking for but reading between the lines could it be summarised as:

1) Most recent StateLog.Timestamp where State is zero

2) State of most recent StateLog.Timestamp

In which case, the following (rather ugly) query would probably work. Assumed the 'Status' column in your group by was a misprint of 'State' as its not returned anywhere.

SELECT
    p.ProductName
    , sl.State AS StateWithLatestTimeStamp
    , MAX(CASE WHEN dt1.State = 0 THEN dt1.MaxTimeStamp ELSE NULL END) AS LatestStateZeroTimeStamp
FROM
    (
    SELECT
        ProductID
        , State
        , MAX(TimeStamp) AS MaxTimeStamp
    FROM
        StateLog
    GROUP BY
        ProductId
        , State
    ) dt1
INNER JOIN
    StateLog sl
ON  sl.ProductID = dt1.ProductID
INNER JOIN
    Product p
ON  p.ProductID = sl.ProductID
GROUP BY
    p.ProductName
    , sl.State
    , sl.TimeStamp
HAVING
    sl.TimeStamp = MAX(dt1.MaxTimeStamp)

Answer 3

 with cte(Productid,TimeStamp,State,Status)  as
(select productid,TimeStamp,State,status,
max(timestamp) over (partition by productid,status) as max1
from statelog
)

 SELECT p.ProductName, 
          dt.MaxTimeStamp, 
          p.Responsible
     FROM Product p
LEFT JOIN(
select productid,max(case when state=0 then TimeStamp else null end) as MaxTimeStamp,
max(case when Timestamp=max1 then state else null end) as MaxState,
from statelog
group by productid,status)
dt ON p.ProductID = dt.ProductID 
ORDER BY p.ProductName;

I am wondering if your logic is a bit confused .I see you are grouping by status but you dont use status anywhere. I could not test this out but if you post table creation script and data population script we can test it out quickly.