Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Posts
    35

    Unanswered: Select query to pull the last/latest event by date

    I have the following table called BADSANTA:
    varchar(30) datetime
    NAME WHENBAD
    OJ Simpson 2007-1-12 xx:xx:xx:xxx
    OJ Simpson 2007-4-2 xx:xx:xx:xxx
    Monica Lewinsky 2006-7-4 xx:xx:xx:xxx
    Monica Lewinsky 2006-10-31 xx:xx:xx:xxx
    Bill Clinton 2006-7-4 xx:xx:xx:xxx
    Bill Clinton 2006-10-31 xx:xx:xx:xxx
    Bart Simpson 2006-11-2 xx:xx:xx:xxx
    Bart Simpson 2006-2-25 xx:xx:xx:xxx
    Bart Simpson 2005-07-27 xx:xx:xx:xxx

    I want the records of the persons latest WHENBAD date. It should return one recordset for each person.

    Thanks in advanced.

  2. #2
    Join Date
    Oct 2007
    Posts
    35
    I run the following query but it still comes up with multiple instances of a user with different dates...

    SELECT Name,[WHENBAD]
    FROM BADSANTA
    WHERE [WHENBAD] IN
    (SELECT
    MAX([WHENBAD])
    FROM
    BADSANTA
    GROUP BY
    Name)
    GROUP BY Name,[WHENBAD]

    Does anyone have a better one?

  3. #3
    Join Date
    Nov 2002
    Posts
    42
    select name,(select max([whenbad]) from BADSANTA where name = tbl.name) whenbad
    from BADSANTA tbl
    group by name
    Last edited by errodr; 10-30-07 at 19:17.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    SELECT name, max(whenbad)
    GROUP BY name

  5. #5
    Join Date
    Oct 2007
    Posts
    35
    Thanks alot, both worked!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •