Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Unanswered: The row number in a SELECT

    How do I get the row number in a SELECT

    Select ???? as Row, Name, Stars From Member


    Row Name Stars
    ---------------------------------------
    1 Enigma ****
    2 BreittKaiser *****
    3 BlindMan *****

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need some sort of unique index or key to sort your results. Then you create a self-join that counts the number of records prior to each record based on the index or key.

    select Members.MemberName, count(*)
    from Members
    inner join Members MemberCount on Members.MemberName >= MemberCount.MemberName
    group by Members.MemberName
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    so I've got to had a table ????

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    so I've got to ADD a table ???

  5. #5
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    can't simply create a user function ?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You don't add a table. You join the table to itself, and when you do this you have to give the second instance a new name or alias so SQL Server can tell which of the two instances you are referring to. In the example I gave, your table is named Members, and when I join it in for the second time I give it the alias MembersCount.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    OK Mr BM
    I'll remember this lesson !! Thx

    but changed my query so that I don't need that column anymore

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Maybe a concrete example would help...

    cut and paste this in to QA and see what it does

    Code:
    USE Northwind
    GO
    
    SELECT l.LastName+', '+l.FirstName, COUNT(*) AS ArtificialOrder
      FROM Employees l INNER JOIN Employees r ON l.LastName+', '+l.FirstName >= r.LastName+', '+r.FirstName
    GROUP BY l.LastName+', '+l.FirstName
    ORDER BY 2
    GO

    Understand though, the order of data in a database is inconsequential...

    in other words Row Number is meaningless.....
    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.

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Thx Mr BK !

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Mr BM....

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

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I hope in French it means something pleasant....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    VERY complicated

    <B>lind<M>an = BM
    <B>rett <K>aiser = BK


    but I don't mind explaining...
    you're americans

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, and in America the initials "BM" stands for "mouvement d'intestin", or "excrément"...

    and "BK" is the initials of a retaurant chain you would call "roi d'hamburger"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    sorry !
    but you'll have to assume your initials
    anyway you named you're self Janitor...

Posting Permissions

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