Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    6

    Question Unanswered: row count in result set

    Does anybody know how I can get a row count in my result set.
    So first column should be 1, 2, 3 ....


    Thanks,
    Boontje

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    If there's a pk in the resultset, you could use that to count with (not null etc). Otherwise I'd suggest a temp table with an identity column.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is producing this result set?

    if it's a simple query against a single table, you could use a ranking self-join, then you won't need a temp table or identity

    however, the ranking must be done based on the values in some column (ascending or descending)

    FYI, Kaiowas, check this out -- IDENTITY() Function Isn't Reliable for Imposing Order on a Result Set
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The argument....

    Is that the optimizer may apply the identity value BEFORE The ORDER BY

    The example given:

    Code:
    SELECT	EmpId, EmpSalary, 
    	LastName, SalaryRank 
    	=IDENTITY(int, 1, 1)
    INTO 	ListOfHighestPaid-
      Employees
    FROM	 Employees
    ORDER BY	 EmpSalary desc
    Can be readdressed as

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    GO
    
    SELECT *, RowNumber=IDENTITY(int, 1, 1) 
    INTO myTable99
    FROM (
    SELECT	TOP 100 PERCENT EmployeeID
    	LastName, FirstName
      FROM Employees
    ORDER BY EmployeeID) AS XXX
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO

    I love the way they show how not to do it, but don't give a solution...except to wait for SQL 2005...when's the release?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    brett, your "readdressed" query isn't guaranteed to work correctly either

    the part about a distributed query is what finally made me see the light

    the solution?? find another way and don't rely on identity!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    brett, your "readdressed" query isn't guaranteed to work correctly either
    How so?

    the part about a distributed query is what finally made me see the light
    Where did that come from?

    the solution?? find another way and don't rely on identity!
    Well sure....an arbitrary number is just that...what meaning will it have?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    How so?
    because your subquery has an ORDER BY in it

    the part about a distributed query comes from here --
    Imagine that the Employees table has 100,000 rows and SQL Server breaks the query into four steps. Each step might be gathering its own set of rows and assigning identity values as SQL Server processes the rows. However, SQL Server wouldn't apply the ORDER BY clause until all four threads are finished gathering rows and SQL Server serializes each of the parallel streams back into a single step. In that context, using IDENTITY() might not give you the results you want.
    yes, an identity is just an arbitrary number -- good of you to notice, because this is actually the crux of the MISuse of identity to impose sequence!

    what meaning will it have? exactly!!!!!

    in particular it won't necessarily reflect the correct order, will it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummm...my derived table will be materialized BEFORE the ORDER BY, so I don't believe there is a problem....

    And yes...this is like beating a dead horse


    I should add this to the blog

    Surrogate Keys - The Devils Spawn
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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