#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    SQL help, grabbing 2 unique values based on a field


    So I have a list sample:
    Order# Location Vendor Name
    --------- --------- ------- -------
    145646842 MLIQUID02T 368308 JOHNNEISHA
    134962284 MLIQUID02T 368308 JERRY
    141138899 MLIQUID02T 368308 CARLENA
    5078916 MLIQUID02T 368308 DONNA
    104577282 MLIQUID02T 368308 JANICE
    13999694 MLIQUID03T 368308 TINA
    175439805 MLIQUID03T 368308 RANDI
    155479792 MLIQUID03T 368308 VICTOR
    24757969 MLIQUID03T 368308 Erick
    181225527 MLIQUID03T 368308 NICOLE
    4801973 MLIQUID05T 368308 DIANA
    55907648 MLIQUID05T 368308 DESIREE
    111406439 MLIQUID05T 368308 AMANDA
    94513944 MLIQUID05T 368308 NANCY
    86444040 MLIQUID05T 368308 DEBRA

    What I'd like to do is run SQL that can create output where I get 2 records from each Location. Example:

    Order# Location Vendor Name
    --------- --------- ------- -------
    145646842 MLIQUID02T 368308 JOHNNEISHA
    134962284 MLIQUID02T 368308 JERRY
    13999694 MLIQUID03T 368308 TINA
    175439805 MLIQUID03T 368308 RANDI
    4801973 MLIQUID05T 368308 DIANA
    55907648 MLIQUID05T 368308 DESIREE

    Personally, I don't need the top value(s), but it would be nice.

    I was trying a few routes with rownum, and I can get it to pull 1 set of pairs with a where location= condition, but I can't seem to successfully combine the two. Any ideas?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Code:
    select Order#, 
           Location,
           Vendor,
           Name
    from (
        select Order#, 
               Location,
               Vendor,
               Name,
               row_number() over (partition by location order by order#) as rn
        from the_unknown_table
    ) t
    where rn <= 2
    You can control which two are picked by adjusting the "order by" part in the partition definition of the windowing function.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo