Post reply

Distinct help

  • September 28, 2010 at 8:40 am

    #229853

    Hi all,

    I need a little help here. I have a table with duplicate agentcodes and agentnames.

    I need to select distinct agentcodes, and also a agentname. The agentname just has to have the agentcode.

    agentcode agentname

    1 Nolrick1

    1 Nolrick2

    1 Nolrick3

    So I want to have the distinct agentcode but the name would be one of the 3 names above.

    Thanks

    KS.

  • September 28, 2010 at 8:43 am

    #1228424

    I'm a little confused.

    Do you mean you want your results to be:

    agentcode agentname

    1 Nolrick1

    Or does it not matter which agentname is returned? Or do you want it without the trailing number? Please post your expected results, and possibly a few more rows of sample data so we can verify.

  • September 28, 2010 at 8:46 am

    #1228427

    usually you'll find the ROW_NUMBER function will do the trick for you here;

    uncomment out the outer SELECT to limit it to just one row, i left the full results as an example so you cna see what it is doing:

    /*

    --results

    RW agentcode agentname

    1 1 Nolrick1

    2 1 Nolrick2

    3 1 Nolrick3

    */

    with myExampleCTE as

    (SELECT 1 as agentcode, 'Nolrick1' as agentname UNION ALL

    SELECT 1 as agentcode, 'Nolrick2' as agentname UNION ALL

    SELECT 1 as agentcode, 'Nolrick3' as agentname

    )

    --SELECT * FROM (

    SELECT

    ROW_NUMBER() OVER (PARTITION BY agentcode ORDER BY agentcode,agentname) AS RW,

    agentcode,

    agentname

    FROM myExampleCTE

    --) X WHERE RW = 1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 28, 2010 at 8:46 am

    #1228428

    Ideally I would want my results to be

    agentcode agentname

    1 Nolrick1

    But any name would be fine.

    agentcode agentname

    1 Nolrick1

    1 Nolrick2

    1 Nolrick3

    0000005 NORDICON SWEDEN

    0000005 NORDIC WAVE AB - STO

    0000005 NORDICON SWEDEN

    0000005 NORDIC WAVE AB - STO

    Ideal results would be

    1 Nolrick1

    0000005 NORDICON SWEDEN

    But any of the names that correspond with the agentcode would be fine.

  • September 28, 2010 at 9:00 am

    #1228451

    The sample Lowell sent works just fine.

    Thanks Lowell, and Derrick.

    KS

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

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