Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unanswered: Need help with grouping !!!

    Hi !

    I have a log table like this :

    UserID LoginDate
    ------ --------------
    31 2005-11-01
    31 2005-11-05
    8 2005-11-02
    31 2005-11-02
    7 2005-11-04


    I need to get the LAST login date for each user as the following result :

    7 2005-11-04
    8 2005-11-02
    31 2005-11-05

    Can someone help me please ?

    Thanks !

  2. #2
    Join Date
    Oct 2005
    Posts
    37
    UserID LoginDate
    ------ --------------
    31 2005-11-01
    31 2005-11-05
    8 2005-11-02
    31 2005-11-02
    7 2005-11-04

    select userid , max(logindate) from tablename
    group by userid

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(USerID int, LoginDate datetime)
    GO
    
    INSERT INTO myTable99(UserID, LoginDate)
    SELECT 31, '2005-11-01' UNION ALL
    SELECT 31, '2005-11-05' UNION ALL
    SELECT 8 , '2005-11-02' UNION ALL
    SELECT 31, '2005-11-02' UNION ALL
    SELECT 7 , '2005-11-04'
    GO
    
    
    SELECT * 
      FROM myTable99 o 
     WHERE EXISTS( SELECT * 
    		 FROM myTable99 i 
    		WHERE i.UserID = o.UserID 
    	     GROUP BY i.UserID
    	       HAVING o.LoginDate = MAX(i.LoginDAte))
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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