UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Row Number, Any Versions    
 
   
ordnance1
post Jul 3 2019, 03:56 PM
Post#1



Posts: 692
Joined: 7-May 11



How can I get the code below to give me the next to last row?

CODE
Select ROW_NUMBER() over(Order By RecordStamp) as ID, * from DataNow
Go to the top of the page
 
WildBird
post Jul 3 2019, 04:22 PM
Post#2


UtterAccess VIP
Posts: 3,632
Joined: 19-August 03
From: Auckland, Little Australia


What are you after? The record count minus 1?

Rownumber is a count basically, so last row should equal the record count. You just want 1 less than that?

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
MadPiet
post Jul 3 2019, 04:33 PM
Post#3



Posts: 3,361
Joined: 27-February 09



No sample data, so I did this with AdventureWorks

I just used ROW_NUMBER() and the sort order inside the OVER window is descending.

CODE
USE AdventureWorks2016_EXT;
GO

SELECT *
FROM
(SELECT BusinessEntityID
    , FirstName
    , LastName
    , ROW_NUMBER() OVER (ORDER BY FirstName DESC) AS rn  /* might need to use ASC depending on which end of the sort you want to pull from */
FROM Person.Person p
WHERE p.PersonType = 'EM'
AND p.LastName = 'Miller') s
WHERE s.rn = 2;

This post has been edited by MadPiet: Jul 3 2019, 04:36 PM
Go to the top of the page
 
nvogel
post Jul 3 2019, 05:09 PM
Post#4



Posts: 1,023
Joined: 26-January 14
From: London, UK


Try this (should work in SQL Server 2012 and later):

SELECT *
FROM DataNow
ORDER BY RecordStamp DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;
Go to the top of the page
 
MadPiet
post Jul 3 2019, 05:39 PM
Post#5



Posts: 3,361
Joined: 27-February 09



I remembered OFFSET just after posting. =(
Go to the top of the page
 
ordnance1
post Jul 4 2019, 06:46 AM
Post#6



Posts: 692
Joined: 7-May 11



Thanks for the helping hand
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2019 - 03:02 AM