Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    55

    Unanswered: Displaying the row number in a query

    Hi,
    My query is retrieving rows from a table.
    All what I need is to display the row number which is simply a consecutive number.
    Can any one advise me if there is a direct thing to do this in the SELECT stmt?
    I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
    In Oracle I can use RowNum in the query.

    Thanks in advance for all.

  2. #2
    Join Date
    Jul 2003
    Posts
    55

    Re: Displaying the row number in a query

    Is it not doable?


    Originally posted by RaedT
    Hi,
    My query is retrieving rows from a table.
    All what I need is to display the row number which is simply a consecutive number.
    Can any one advise me if there is a direct thing to do this in the SELECT stmt?
    I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
    In Oracle I can use RowNum in the query.

    Thanks in advance for all.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The short answer is NO. TSQL operations are set based, and no ordering is guaranted by the server unless the developer or programmer specifies it, thus row numbering is pointless because the same query run on two different occasions could result in the same record being assigned different row numbers.

    The long answer is YES, if your result set is sorted by a unique key or combination of columns, then you can write a Select statement that loops back on itself and counts the number of records less than each record. This is an expensive query to run and can be difficult to debug, so my recommendation to you would be to use a temporary table (actually, a table variable is more efficient) as long as it suits your needs.

    Why do you need the results numbered? While there are some circumstances where this is beneficial, it is often a sign of problems with the database schema or the application design concept.

    blindman

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    If you don't have already a client, I may consider to put your SELECT statement in the software of a (ADO) client, where you can make use of the AbsolutePosition property of a recordset.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •