Results 1 to 8 of 8

Thread: rank results

  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: rank results

    Hi all,

    Im trying to make a rank of sales. Ive to important columns, the money spent, called [importe anual] and the user id called [nif] and used a query like this:

    Code:
    SELECT SUM([importe anual]) importe,nif FROM [puntos importe]
    GRUOP BY nif ORDER BY importe desc
    and i get the results in a table that looks like:

    importe nif

    1335 234
    1290 786
    992 123

    but i want to know the row position in the ranking of sales to tell the user "youre Nth in the sales ranking". I guess that i need to do a query that return something like:

    importe nif pos

    1335 234 1
    1290 786 2
    992 123 3

    and the use a WHERE to get the desired position.

    I think this must be a common problem but cant find any answers in google, maybe im doing the worng question.

    Excuse my english

    Thanks in advance

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: rank results

    I believe that similar questions have been posted here before; as I recall, the answer has been to INSERT the data into a temporary table that has an IDENTITY column (data must be inserted in the correct order). Then use the IDENTITY column to determine the relative "ranking" for the row.

    Alternatively, I suppose you could use a cursor, but I think that would probably be slower and there is general consensus among the forum members here to avoid cursors wherever/whenever possible.

    I hope that this helps, I wish you good luck.

    hmscott

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [sniped]
    How about...

    Code:
    USE Northwind
    GO
    
    DECLARE @x TABLE(EmployeeID int, Orders int, Rank int IDENTITY(1,1))
     
    INSERT INTO @x (EmployeeId, Orders)
      SELECT EmployeeID, SUM(OrderId) AS Orders FROM Orders
    GROUP BY EmployeeId
    ORDER BY 2 DESC
    
    SELECT * FROM @x ORDER BY Rank
    GO
    [/sniped]
    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.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Should you not wish to use a temp table or cursor.

    select t.a, (select count(*) from table where a <= t.a) as rownum
    from table t
    order by t.a;

    Each instance of t.a must be unique othewise duplicate positions will occur.

    It would be faster though to use DBMS code, as no joins / sub-queries would be required.
    Last edited by r123456; 12-23-03 at 13:18.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Dec 2003
    Posts
    2

    Re: rank results

    It worked perfectly!!!

    Its the first time i use a temorary table. Im a newbie with SQL. Seems pretty useful.

    Only one question, when is the temorary table destroyed? after the select, after the SP execution or after connection drop?

    Thanks

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245

    Re: rank results

    From SQL BOL:
    Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

    A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.


    All other local temporary tables are dropped automatically at the end of the current session.


    Global temporary tables (##TableName) are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I always drop my temp tables...just part of good coding practices..

    And what I gave you was a TABLE Variable, new to SQL 2000.

    I believe BOL say to use table variables because the cause less contention...


    I do like r123456 solution though...have to see if I can get it to work with the SUM(...)
    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.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Absolutely, use table variables when possible over temp tables.

Posting Permissions

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