Sql Query With GroupBys - Impossible Query

Question

I have the following:

Name    Age When
Paul    21  01-Jan-10
Paul    54  01-Jan-11
Paul    65  01-Jan-12

I want to pull out all names and that age wherer the date is >= 01-Jan-11

I ve tried

SELECT NAME, AGE, MIN(When)
FROM ATABLE
WHERE When >= '01-Jan-11'
GROUP BY NAME, AGE

That did not work - I get the 01 Jan 2011 AND 01 Jan 2012 for Paul which is wrong - I just want the one

NOTE: This comment is the most correct so far but does not provide an answer :(

You're where clause will get 2 records and will keep them as 2 records since you're grouping by Name and Age. If Name and Age were the same, it'd be 1 record and not 2.

Answer 1

try

SELECT NAME, AGE, MIN(When) FROM A TABLE WHERE When >= '2011/01/01' GROUP BY NAME, AG

http://www.databasejournal.com/features/mssql/article.php/2209321/Working-with-SQL-Server-DateTime-Variables-Part-Three---Searching-for-Particular-Date-Values-and-Ranges.htm

Answer 2

In SQL Server:

SELECT  a.*
FROM    (
        SELECT  DISTINCT name
        FROM    atable
        ) ad
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    atable ai
        WHERE   ai.name = ad.name
                AND When >= '01-Jan-11'
        ORDER BY
                When
        ) a

Answer 3

WHEN is a SQL Server reserved word - if that is really your column name it might be causing you problems. In any case, it would be helpful to post more information, such as any errors you were receiving.

Also for what its worth, normally I would use DATEDIFF for the date comparison:

  SELECT NAME, AGE, MIN(When)
    FROM ATABLE
   WHERE DATEDIFF ( 'd', '2001-01-01', When ) > 0
GROUP BY NAME, AGE

Answer 4

thats easy...

SELECT NAME, AGE, MIN(When)
FROM ATABLE
WHERE 
    YEAR([When]) >= 2011
    AND MONTH([When]) >= 1
    AND DAY([When]) >= 1
GROUP BY NAME, AGE

Answer 5

SELECT  k.Name, t.Age, k.MinWhen
FROM    (
        SELECT  Name, MIN(When) MinWhen
        FROM    ATABLE
        GROUP BY Name
        ) k
        JOIN ATABLE t ON
            t.Name = k.Name
        AND t.When = k.MinWhen