Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Sep 2009
    Posts
    34

    Unanswered: fetching last entries record

    Table 1
    Name(field) visit_date(field)
    Jim 27/06/2011
    Kim 11/09/2012
    Bright 24/03/2011
    Bright 27/03/2012
    Kim 17/10/2012
    Jim 23/08/2012
    Lee 27/06/2012
    Lee 26/04/2013
    Jim 07/08/2012
    how to select max visit_date data records from table 1 like selection table displayed
    Selection Table
    Name(field) visit_date(field)
    Bright 27/03/2012
    Jim 23/08/2012
    Kim 17/10/2012
    Last edited by oryazi; 06-03-13 at 10:16.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The best method would be to use a CTE (Common Table Expression) and the ROW_ORDER() function (descending).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Both solutions work.
    The second solution is the best if you want other columns on the most recent record.
    Code:
    CREATE TABLE DaTable(
    	Name	CHAR(10)	NOT NULL,
    	visit_date	DATE	NOT NULL
    );
    
    INSERT INTO DaTable(Name, visit_date)
    SELECT 'Jim', CONVERT(DATE, '27/06/2011', 103) UNION ALL
    SELECT 'Kim', CONVERT(DATE, '11/09/2012', 103) UNION ALL
    SELECT 'Bright', CONVERT(DATE, '24/03/2011', 103) UNION ALL
    SELECT 'Bright', CONVERT(DATE, '27/03/2012', 103) UNION ALL
    SELECT 'Kim', CONVERT(DATE, '17/10/2012', 103) UNION ALL
    SELECT 'Jim', CONVERT(DATE, '23/08/2012', 103) UNION ALL
    SELECT 'Lee', CONVERT(DATE, '27/06/2011', 103) UNION ALL
    SELECT 'Lee', CONVERT(DATE, '26/04/2013', 103) UNION ALL
    SELECT 'Jim', CONVERT(DATE, '07/08/2012', 103)
    
    SELECT * FROM DaTable
    
    --Solution 1 with GROUP BY 
    SELECT Name, MAX(visit_date) as max_visit_date
    FROM DaTable
    GROUP BY Name
    GO
    
    --Solution 2 with Common Table Expression (CTE)
    WITH CTE AS (
    SELECT Name, 
    	visit_date,
    	ROW_NUMBER() OVER (PARTITION BY Name ORDER BY visit_date DESC) as RowNum
    FROM DaTable
    )
    SELECT Name, visit_date
    FROM CTE
    WHERE RowNum = 1
     
    DROP TABLE DaTable
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Sorry Blindman, our posts crossed. I didn't know you had already answered it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One solution would be:
    Code:
    DECLARE @t TABLE (
       name         VARCHAR(19)     NOT NULL
    ,  visit_date   DATE            NOT NULL
       )
    
    INSERT INTO @t (name, visit_date)
       VALUES  ('Jim', '2011-06-27'), ('Kim',    '2012-09-11')
    ,     ('Bright',   '2011-03-24'), ('Bright', '2012-03-27')
    ,     ('Kim',      '2012-10-17'), ('Jim',    '2012-08-23')
    ,     ('Lee',      '2012-06-27'), ('Lee',    '2013-04-26')
    ,     ('Jim',      '2012-08-07')
    
    SELECT name, visit_date
       FROM
          (SELECT name, visit_date
    ,	     Row_Number() OVER (PARTITION BY name ORDER BY visit_date DESC) AS r
    	     FROM @t) AS z
       WHERE 1 = r
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wow, we're on a multi-posting roll today!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Sep 2009
    Posts
    34
    thanks 4 reply but the problem is not solved
    i have
    Table 1
    Customers_Name(field) visit_date(field)
    Jim 27/06/2011
    Kim 11/09/2012
    Bright 24/03/2011
    Bright 27/03/2012
    Kim 17/10/2012
    Jim 23/08/2012
    Lee 27/06/2012
    Lee 26/04/2013
    Jim 07/08/2012
    i want to select all customers last visit_date data records from table 1 like selection displayed
    Selection
    Customers_Name(field) visit_date(field)
    Bright 27/03/2012
    Jim 23/08/2012
    Kim 17/10/2012

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Why is Lee left out?

  9. #9
    Join Date
    Sep 2009
    Posts
    34
    sorry i forget lee
    thanks 4 reply
    i have
    Table 1
    Customers_Name(field) visit_date(field)
    Jim 27/06/2011
    Kim 11/09/2012
    Bright 24/03/2011
    Bright 27/03/2012
    Kim 17/10/2012
    Jim 23/08/2012
    Lee 27/06/2012
    Lee 26/04/2013
    Jim 07/08/2012
    i want to select all customers last visit_date data records from table 1 like selection displayed
    Selection
    Customers_Name(field) visit_date(field)
    Bright 27/03/2012
    Jim 23/08/2012
    Kim 17/10/2012
    Lee 26/04/2013

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    So, what was the result when you tried one of the solutions above?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    WIM's solution #2 should satisfy that requirement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman View Post
    WIM's solution #2 should satisfy that requirement.
    Do you see a problem with mine? It generates the required result set when I run it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Sep 2009
    Posts
    34
    wim's solution 2 is not working on sql server 2000
    give syntax error msg

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    OK, so what does Pat's solution give you? His is probably more SQL 2000 friendly.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think Row_Number() was implemented in 2000.
    We're going to need a solution implementing stone knives and clovis points.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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