Post reply

query current row count

  • March 3, 2006 at 5:17 am

    #171348

    Hi all,

    I am trying to figure out if there is a way to get the currentrow number of the query as a column.  For example, if a query contains 5 records then I need a column that shows

    column1| ...

    1|...

    2|...

    3|...

    4|...

    5|...

    thx in adv.

    newbie

  • March 3, 2006 at 6:30 am

    #624573

    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

     

  • March 3, 2006 at 6:43 am

    #624584

    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.:-|

  • March 3, 2006 at 6:45 am

    #624587

    There's no built in function to do this.

    The self join doesn't use temp tables.

  • March 3, 2006 at 6:55 am

    #624590

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

    Thanks again for the help 🙂

  • March 3, 2006 at 3:58 pm

    #624750

    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

     

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

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