Post reply

Code To Return Highest Value (rank) not working - why ?

  • July 14, 2009 at 12:48 pm

    #208338

    SELECT * FROM (select id3, email1, dense_rank() OVER (order BY id3) rank from outputresume3) WHERE RANK=N;

    Any idea why this code does not work ?

    I am trying to get the emails with the highest numerical ID3 number.

    Thanks

  • July 14, 2009 at 1:17 pm

    #1024654

    How about some sample data to show that your code does not work?

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 14, 2009 at 1:24 pm

    #1024660

    It would help if you'd provide the DDL for your table, sample data in a readily consummable format (that can be cut/paste/run in SSMS to load the table created by your DDL), expected results based on the sample data.

    For help with this request please read the first article referenced below in my signature block.

  • July 14, 2009 at 1:37 pm

    #1024671

    Sample data:

    col(id3) col(email1)

    454382abc@hotmail.com

    612832john@global.net

    612833john@global.net

    612834john@global.net

    I would like the results to be:

    454382abc@hotmail.com

    612834john@global.net

    Since the 454382 record is unique and the 612834 record is the highest id3 (most recent).

    Thanks

  • July 14, 2009 at 1:43 pm

    #1024678

    DECLARE @Table TABLE (id3 int, email1 varchar(256))

    INSERT INTO @Table

    SELECT 454382, 'abc@hotmail.com' UNION ALL

    SELECT 612832, 'john@global.net' UNION ALL

    SELECT 612833, 'john@global.net' UNION ALL

    SELECT 612834, 'john@global.net'

    SELECT * FROM @Table

    SELECT MAX(id3) as id3, email1

    FROM @Table

    GROUP BY email1

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 14, 2009 at 2:03 pm

    #1024693

    Many Thanks !

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

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