Help-Obatining 2nd last record

biznez · #1 (**permalink**) 01-06-12, 17:09

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

gvee · #2 (**permalink**) 01-06-12, 21: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

biznez · #3 (**permalink**) 01-09-12, 09:40

Quote:

Originally Posted by gvee

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

r937 · #4 (**permalink**) 01-09-12, 10:05

Quote:

Originally Posted by biznez

what could be wrong

what could be wrong? you're still on an ancient version of sql server

biznez · #5 (**permalink**) 01-09-12, 10:18

Damn, is there another alternative to do this?

r937 · #6 (**permalink**) 01-09-12, 10:34

Quote:

Originally Posted by biznez

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

Brett Kaiser · #7 (**permalink**) 01-09-12, 16:17

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

biznez · #8 (**permalink**) 01-10-12, 09:45

Quote:

Originally Posted by Brett Kaiser

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

Brett Kaiser · #9 (**permalink**) 01-10-12, 13:37

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

watson88 · #10 (**permalink**) 01-12-12, 07:35

I recently use this query means top 2. So i can say its a better and easy way.

Quote:

Originally Posted by Brett Kaiser

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 Kaiser · #11 (**permalink**) 01-12-12, 11:13

They wanted the 2nd by an ID

my First one was the second across the set of data

biznez · #12 (**permalink**) 02-23-12, 20:01

Quote:

Originally Posted by Brett Kaiser

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

Internet Services

Web Development

Digital Marketing

Consumer Tech