query current row count

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Joe Nakanishi-3 SSC Eights! Points: 828 More actions · Answer 1

You can do a self join or put your data into a temp table/table variable that has an identity and select from that table.

Self Join...

USE PUBS

GO

SELECT (SELECT COUNT(au_id)

FROM Authors a1

WHERE a1.au_id >= a2.au_id) AS RowID,

a2.*

FROM Authors a2

ORDER BY RowID

Table variable...

DECLARE @RowCount TABLE

(

RowID INT IDENTITY(1, 1) NOT NULL,

LName VARCHAR(50) NULL,

FName VARCHAR(50) NULL

)

INSERT INTO @RowCount

SELECT au_lname, au_fname

FROM Authors

SELECT * FROM @RowCount

sqltool SSC Rookie Points: 39 More actions · Answer 2

Thanks Joe, but I was looking for something without the use of temp tables.

Something more of a global variable, a system function or using aggregate function that will retrieve the current row. Can this be done?

thanks again for the reply. and of course if there is no other way then, guess I will need to use what is available.:-|

Joe Nakanishi-3 SSC Eights! Points: 828 More actions · Answer 3

There's no built in function to do this.

The self join doesn't use temp tables.

sqltool SSC Rookie Points: 39 More actions · Answer 4

well, then I will give it a shot. Let you know if everything is A-Ok

Thanks again for the help 🙂

Rob v Dijk SSC Enthusiast Points: 163 More actions · Answer 5

what aboout this...

declare @rowcount tinyint

declare @row tinyint

select blablabla this returns your 5 records or whatever the number maybe

@@rowcount will tell you how many rows were affected by your previous statement, the select in this case.

SELECT @rowcount = @@ROWCOUNT

while @row <= @rowcount

begin

insert sometable

@row

set @row = @row + 1

end