Phil K Phil K - 1 year ago 55
SQL Question

I need the most recent value in a GROUP BY - CASE expression

I'm trying to make a

CASE
expression in T-SQL that's in a
GROUP BY
clause, that's basically asking if there's a
a.id
THEN provide
b.name
associated with it (
where a.id = b.id
) .

What I have so far is: (Updated Query)

SELECT b.name, ...
MAX(CASE
WHEN a.id IS NOT NULL
THEN b.name END)
FROM ...
LEFT JOIN table_b AS b
ON b.id = a.id
GROUP BY ...


Because it's T-SQL, the CASE has to be in a aggregate function or GROUP BY clause, which is why I included the
MAX
. However, without the GROUP BY clause, there would be 4 values. I need the most recent value as defined by a.datetime. How can I put that condition in the CASE statement?

Answer Source

Your last edit changes quite a lot. You should use ROW_NUMBER for this:

WITH CTE AS
(
    SELECT  b.name, ...
            RN = ROW_NUMBER() OVER( PARTITION BY a.id 
                                    ORDER BY b.DateColumn DESC)
    FROM ...
    LEFT JOIN table_b AS b
       ON b.id = a.id
)
SELECT  *,
        CASE 
            WHEN a.id IS NOT NULL 
            THEN b.name
        END
FROM CTE 
WHERE RN = 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download