Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2011
    Posts
    24

    Unanswered: Help-Obatining 2nd last record

    Hi,
    what is the query to get 2nd last record from a table on a specific note_id key?

    this is the data i have
    Note_id ======From Date===== =======To Date======
    892481====2010-07-27 00:00:00===2010-07-27 00:00:00.000
    892481====2010-07-27 00:00:00===2010-07-27 23:36:30.063
    892481====2010-11-01 00:00:00===2010-11-02 00:15:23.027
    892481====2011-03-08 00:00:00===2011-03-08 13:18:37.873 <====== This is the result i want
    892481====2011-04-21 00:00:00===2011-04-21 07:57:20.567

    please and thank you
    Last edited by biznez; 01-06-12 at 19:12.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Something like this should do it
    Code:
    SELECT note_id
          , from_date
          , to_date
    FROM   (
          SELECT note_id
                , from_date
                , to_date
                , Row_Number() OVER (PARTITION BY note_id ORDER BY from_date DESC, to_date DESC) As row_num
          FROM   dbo.your_table
         ) As x
    WHERE  row_num = 2
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2011
    Posts
    24
    Quote Originally Posted by gvee View Post
    Something like this should do it
    Code:
    SELECT note_id
          , from_date
          , to_date
    FROM   (
          SELECT note_id
                , from_date
                , to_date
                , Row_Number() OVER (PARTITION BY note_id ORDER BY from_date DESC, to_date DESC) As row_num
          FROM   dbo.your_table
         ) As x
    WHERE  row_num = 2

    Hello gvee...thanks for your help but this does not work.
    i get this error

    "Msg 195, Level 15, State 10, Line 8
    'Row_Number' is not a recognized function name."

    what could be wrong
    thanks buddy

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by biznez View Post
    what could be wrong
    what could be wrong? you're still on an ancient version of sql server
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2011
    Posts
    24
    Damn, is there another alternative to do this?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by biznez View Post
    Damn, is there another alternative to do this?
    there are many

    i hope you don't mind my saying so, but "second largest column value" is a common recurring sql homework question, e.g. second largest salary

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT TOP 1 [To Date] FROM (
    (SELECT TOP 2 [To date] FROM your Table ORDER BY [To Date] DESC) AS XXX
    ORDER BY [To Date] ASC
    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.

  8. #8
    Join Date
    Nov 2011
    Posts
    24
    Quote Originally Posted by Brett Kaiser View Post
    SELECT TOP 1 [To Date] FROM (
    (SELECT TOP 2 [To date] FROM your Table ORDER BY [To Date] DESC) AS XXX
    ORDER BY [To Date] ASC
    Thanks for this Brett but is it possible to do this per note_id. I mean i have a large database with multiple note_id's. How can i pull the 2nd last record for each note_id?

    Thanks again for all your help

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    CREATE TABLE #myTable99(Note_Id int, To_Date datetime)
    GO
    
    INSERT INTO #myTable99(Note_ID, To_Date)
    SELECT 892481, '2010-07-27 00:00:00.000' UNION ALL
    SELECT 892481, '2010-07-27 23:36:30.063' UNION ALL
    SELECT 892481, '2010-11-02 00:15:23.027' UNION ALL
    SELECT 892481, '2011-03-08 13:18:37.873' UNION ALL  -- 2nd Highest for 892481
    SELECT 892481, '2011-04-21 07:57:20.567' UNION ALL
    SELECT 892482, '2010-07-27 00:00:00.000' UNION ALL
    SELECT 892482, '2010-07-27 23:36:30.063' UNION ALL  -- 2nd Highest for 892482
    SELECT 892482, '2010-11-02 00:15:23.027' UNION ALL
    SELECT 892483, '2011-03-08 13:18:37.873' UNION ALL  -- 2nd Highest for 892483
    SELECT 892483, '2011-04-21 07:57:20.567'
    
    -- Find Max Date per Note_ID
    
    SELECT Note_Id, MAX(To_Date)
    FROM #myTable99
    GROUP BY Note_ID
    
    -- Find The One Before
    
    SELECT Note_Id, MAX(To_Date)
      FROM #myTable99 o
     WHERE EXISTS (SELECT Note_Id
    				 FROM #myTable99 i
    			    WHERE o.Note_ID = i.Note_Id
    			 GROUP BY Note_ID
    			   HAVING o.To_Date < MAX(i.To_Date))
    GROUP BY Note_ID
    GO
    
    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.

  10. #10
    Join Date
    Jan 2012
    Posts
    1
    I recently use this query means top 2. So i can say its a better and easy way.


    Quote Originally Posted by Brett Kaiser View Post
    SELECT TOP 1 [To Date] FROM (
    (SELECT TOP 2 [To date] FROM your Table ORDER BY [To Date] DESC) AS XXX
    ORDER BY [To Date] ASC

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    They wanted the 2nd by an ID

    my First one was the second across the set of data
    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.

  12. #12
    Join Date
    Nov 2011
    Posts
    24

    Lightbulb

    Quote Originally Posted by Brett Kaiser View Post
    Code:
    CREATE TABLE #myTable99(Note_Id int, To_Date datetime)
    GO
    
    INSERT INTO #myTable99(Note_ID, To_Date)
    SELECT 892481, '2010-07-27 00:00:00.000' UNION ALL
    SELECT 892481, '2010-07-27 23:36:30.063' UNION ALL
    SELECT 892481, '2010-11-02 00:15:23.027' UNION ALL
    SELECT 892481, '2011-03-08 13:18:37.873' UNION ALL  -- 2nd Highest for 892481
    SELECT 892481, '2011-04-21 07:57:20.567' UNION ALL
    SELECT 892482, '2010-07-27 00:00:00.000' UNION ALL
    SELECT 892482, '2010-07-27 23:36:30.063' UNION ALL  -- 2nd Highest for 892482
    SELECT 892482, '2010-11-02 00:15:23.027' UNION ALL
    SELECT 892483, '2011-03-08 13:18:37.873' UNION ALL  -- 2nd Highest for 892483
    SELECT 892483, '2011-04-21 07:57:20.567'
    
    -- Find Max Date per Note_ID
    
    SELECT Note_Id, MAX(To_Date)
    FROM #myTable99
    GROUP BY Note_ID
    
    -- Find The One Before
    
    SELECT Note_Id, MAX(To_Date)
      FROM #myTable99 o
     WHERE EXISTS (SELECT Note_Id
    				 FROM #myTable99 i
    			    WHERE o.Note_ID = i.Note_Id
    			 GROUP BY Note_ID
    			   HAVING o.To_Date < MAX(i.To_Date))
    GROUP BY Note_ID
    GO
    
    DROP TABLE #myTable99
    GO
    Hi Brett, first i like to say thanks for the query. it works fine but i wanted to know what if there is no 2nd last record for that specific note_id? i would like to keep this record and not exclude it from my result. How would u go about doing this?
    Thanks again

Posting Permissions

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