[Solved] Where Clause OverRides Max()

Question

Now that your requirements are clear, I think this is what you need:

SELECT ID, saledate, empName, saleStatusCode
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY saledate DESC) RowOrder, *
    FROM #Test
    ) SUBQ
WHERE RowOrder = 1

In your case, the result is:

1   2014-12-12 00:00:00.000 Joe Joe Joe 2

Actually the result would show Mark, because its date is from 3014, but I guess that was a typo. 😉

Important edit:

Oh, John H’s comment made me rethink this and I found out that I was totally overthinking your problem!! Your request could have been solved as easily as this:

SELECT TOP 1 *
FROM #Test
WHERE saleStatusCodeIN (1, 2, 3, 8, 122, 14, 22)
ORDER BY saledate DESC

How could I have missed that?

Accepted Answer