Click here to Skip to main content
15,882,278 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
dear all...

how can i select the second last row of the column of a table using a SQL


thanx in advance
Posted
Comments
CS2011 9-Jun-11 5:33am    
Which Database you are using ? Oracle or SqlServer ?
Om Prakash Pant 9-Jun-11 5:38am    
and based on what criteria?

1 solution

Hi,

Please check the below code.

SELECT * FROM (SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY c.TableColumn_PK ASC),c.* 
FROM TableName c) AS CD WHERE RowNumber = ((SELECT COUNT(*) FROM TableName)-1)


Just replace "TableColumn_PK" with your primary key and "TableName" with your table name.

I tested it. It will work fine.

Cheers....

Update: There are a lot of solutions too for this. Try to find out others.

Update:

As you posted an answer by stating that you are using sql server and you want the date difference. So, please use the code given below.

SQL
DECLARE @VAR TABLE (KeyID BIGINT IDENTITY(1,1),RANK BIGINT,LastTwoDates DATETIME)
INSERT INTO @VAR
SELECT TOP(2) RANK() OVER (ORDER BY dLastLogindate) AS RANK, dLastLogindate
FROM tbUS_Recruiter
ORDER BY RANK DESC
IF ((SELECT COUNT(*) FROM @VAR) = 2)
BEGIN
    SELECT DATEDIFF(DAY,(SELECT LastTwoDates FROM @VAR WHERE KeyID = 2),(SELECT LastTwoDates FROM @VAR WHERE KeyID = 1)) AS DayCount,
           DATEDIFF(HOUR,(SELECT LastTwoDates FROM @VAR WHERE KeyID = 2),(SELECT LastTwoDates FROM @VAR WHERE KeyID = 1)) AS HourCount,
           DATEDIFF(MINUTE,(SELECT LastTwoDates FROM @VAR WHERE KeyID = 2),(SELECT LastTwoDates FROM @VAR WHERE KeyID = 1)) AS MinutesCount,
           DATEDIFF(SECOND,(SELECT LastTwoDates FROM @VAR WHERE KeyID = 2),(SELECT LastTwoDates FROM @VAR WHERE KeyID = 1)) AS SecondCount
END


Just change "dLastLogindate" field into your date field and "tbUS_Recruiter" as your table name. It will work fine.

Code checked.

Cheers...
 
Share this answer
 
v3
Comments
CS2011 9-Jun-11 6:06am    
Yep You were correct. My soluation was wrong Indeed.5+ for this one
arindamrudra 9-Jun-11 6:06am    
Thanks a lot.
Sandeep Mewara 9-Jun-11 11:23am    
My 5 for the effort!
arindamrudra 10-Jun-11 0:03am    
Thanks a lot Sandeep.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900