September 28, 2010 at 8:40 am
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
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
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
RWagentcodeagentname
11Nolrick1
21Nolrick2
31Nolrick3
*/
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
September 28, 2010 at 8:46 am
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
0000005NORDICON SWEDEN
0000005NORDIC WAVE AB - STO
0000005NORDICON SWEDEN
0000005NORDIC WAVE AB - STO
Ideal results would be
1 Nolrick1
0000005NORDICON SWEDEN
But any of the names that correspond with the agentcode would be fine.
September 28, 2010 at 9:00 am
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