Select 4th row only - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select 4th row only

Author

Topic

jgurgen
Starting Member

4 Posts

Posted - 2007-04-05 : 15:35:32

i have a table with a loanid and a historycounter. The historycounter is a unique identifier for each transaction per loanid. the historycounter isnt in sequential order meaning, some of them could be in order of 2,3,4,5 or it could be 2,5,8,19.

I need to do an update to each row to set balance=tranamt

I was thinking of doing a select count to get the number of rows then do a loop to update each one, but because of the varying counters its not that simnple. So is there a way to select a certain row number to update it?

Like using SELECT TOP 1 gives the first row, is there some kind of SELECT ROW 2 or something?

Or is there a better way to do this?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-05 : 17:15:12

In 2000 it is tricky, but in 2005 it is pretty simple, try this

WITH Ordered AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY historycounter) AS RowNumber
FROM Loans
WHERE loanid = 12345)
SELECT * 
FROM Ordered
WHERE RowNumber = 2

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-06 : 04:13:03

you could always do

select top 1 a.* from
(select top 4 b.* from loans b order by historycounter desc) a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources