Fetching second last row from sql server table

• Ninad Thakur

  SSC Veteran

  Points: 221

  More actions

  April 26, 2006 at 11:12 pm

  #60952

  Hi Everybody

      I am a software developer in Nashik, Maharashtra, India and a free subscriber of e-newsletters at sqlservercentral.com.

   

  I just want can you please guide me on how do I retrieve a second last row from sql server table.

   

  I tried @@identity and scope_identity, but in vain

   

  and let me clear I want to fetch this second last row in a single sql query (may contain joins or subqueries)

   

  and as you know there is no ROWNUM() function in sql server 2000

   

  so please guide me what could be the best solution

   

  Thanking you in anticipation

   

  Kind regards

  Ninad

• paul_richardson

  SSCarpal Tunnel

  Points: 4594

  More actions

  April 27, 2006 at 3:40 am

  #634508

  hi,

  do you have a particular table in mind ? if so post the DDL and i'm sure someone can help you out

  Thanks

  Paul

• paul_richardson

  SSCarpal Tunnel

  Points: 4594

  More actions

  April 27, 2006 at 4:38 am

  #634518

  hi again,

  this also might benefit from being posted in another forum, maybe SQL Server 7.0/2000 T-SQL.

  Paul

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  April 27, 2006 at 10:24 am

  #634633

  Well I am not sure if I got your question right but you can try this.

  CREATE

  TABLE client

  (clientID int NOT NULL IDENTITY(1,1),

  ClientName

  varchar(255))

  GO

  INSERT

  INTO dbo.client

  SELECT

  'Microsoft'

  INSERT

  INTO dbo.client

  SELECT

  'Apple'

  INSERT

  INTO dbo.client

  SELECT

  'Sun'

  INSERT

  INTO dbo.client

  SELECT

  'IBM'

  GO

  SELECT

  TOP 1 * FROM

  dbo.client

  WHERE

  clientID NOT IN (SELECT TOP 1 clientID FROM dbo.client ORDER BY clientID DESC) ORDER BY clientID DESC

   

   

  Prasad Bhogadi
  www.inforaise.com

• AndrewMurphy

  SSCertifiable

  Points: 5586

  More actions

  April 28, 2006 at 2:33 am

  #634760

  Remember LAST is RELATIVE to some IDENTIFIABLE and PREDICATABLE ORDER on your data...the key is the ORDER BY clause.

  This may be shorter/faster?

  SELECT TOP 1 * FROM (SELECT TOP 2 * FROM dbo.client ORDER BY clientID DESC) a ORDER BY a.clientID

• lindss01

  SSCrazy

  Points: 2264

  More actions

  April 28, 2006 at 2:54 pm

  #635010

  My Colleague, who I still owe a dinner, came up with this solution (I just coded it) But we both like the previous one...

  SELECT

  TOP 1 * FROM

  dbo

  .client

  WHERE

  clientID in (SELECT max(clientID) from dbo.client Where clientID < (SELECT MAX(clientID) from dbo.client))