SELECT MAX Date T-SQL Subquery

Question

I am attempting to SELECT rows from a table using a query like this

SELECT     pminf_member, pminf_schmem
         , pminf_date, pminf_fund
         , pminf_cont, pminf_rate
         , pminf_matrix
FROM   pe_minvf
WHERE (pminf_member = 4380) 
  AND (pminf_schmem = 'M') 
  AND (pminf_date <= '03/30/2011')
  AND (pminf_date =
           (SELECT MAX(pminf_date) AS Expr1
            FROM   pe_minvf AS pe_minvf_1
            WHERE  (pminf_member = 4380)
           )
       )
  AND (pminf_fund = 'LIFESTYLE')

What I should be getting from my subquery (I think) is a date of '01/01/2011' but when I run my query I am getting no results back.

If I replace the subquery with the hardcoded date I get the correct rows returned. For example

SELECT  pminf_member, pminf_schmem
      , pminf_date, pminf_fund
      , pminf_cont, pminf_rate
      , pminf_matrix
FROM  pe_minvf
WHERE (pminf_member = 4380)
  AND (pminf_schmem = 'M')
  AND (pminf_date <= '03/30/2011')
  AND (pminf_date = '01/01/2011')
  AND (pminf_fund = 'LIFESTYLE')

This query returns the correct results.

Any ideas why the subquery is not returning the max date or if it is, why am I getting no rows back?

Thanks, Tristan

Answer 1

You filter on different conditions in your queries.

It's pminf_fund = 'LIFESTYLE' in the subquery but pminf_schmem = 'M' in the outer query.

Also, you limit the date in the outer query and don't do it in the subquery.

If you just need the most recent record up to '03/30/2011', use this:

SELECT  TOP 1
        pminf_member, pminf_schmem, pminf_date, pminf_fund, pminf_cont, pminf_rate, pminf_matrix
FROM    pe_minvf
WHERE   pminf_member = 4380
        AND pminf_schmem = 'M'
        AND pminf_fund = 'LIFESTYLE'
        AND pminf_date <= '03/30/2011'
ORDER BY
        pminf_date DESC