April 30, 2015 at 10:13 am
Hello,
Below is my sample data. I can't figure out how to select Unique phonenumber contacts for the same Ranked values from the set.
Basically the table is a mix of contactIDs. Some of them have duplicate phone numbers and through a separate mechanism we have ranked them.
It's easier then to pull out max(ranked) CLI_Numbers and their counterpart contactID(s).
But I am also getting 2 or more records where the rank happens to be the same. I don't want that.
Any one of the contactID will do for me.
The table has also same cliNumbers with different rank values, which are then correctly being picked up in the query below.
Note: ContactId is a unique value for each person in the table.
RecordID is simply RowID.
( I have attempted to populate a sample data suited for this forum - not sure how it comes out on the browser)
Any help will be appreciated.
Thank you,
Bij
if object_id('tempdb..#MyData') is not null
drop table #MyData
create table #MyData
(
RecordID int,
contactID int,
forename varchar(25),
surname varchar(25),
cliNumber varchar(15),
ranked_value smallint
)
insert #MyData
select 1,3000,'Joe', 'Bloggs','0123459998',25 union all
select 2,3001,'Joey', 'Blogs','0123459998',25 union all
select 3,3002,'Fred', 'Bloggs','0153451116',60 union all
select 4,3003,'John', 'Blogs','0123452238',35 union all
select 5,3004,'Mary', 'Bloggs','0123452238',35 union all
select 7,3005,'Alex', 'Blogs','0199982345',15 union all
select 8,3010,'Bij', 'Freddy','0199982345',15 union all
select 9,3011,'James', 'Johns','0177453238',75
select 4,3012,'Mark', 'Parker','0197321229',50 union all
select 5,3010,'Jen', 'Parker','0197321229',20 union all
select * from #MyData
This is my query attempt
With RankedmobileDuplicateSet
as(
select cliNumber, max(ranked_value) as ranked_max_value
from #temp_UK_mobiledata
group by cliNumber)
select contactID, CLINumber
from #temp_UK_mobiledata
where exists (
select 1 from RankedmobileDuplicateSet t2
where t.CLINumber= t2.CLINumber
and t.ranked_value = t2.ranked_max_value
)
April 30, 2015 at 10:46 am
will this work?
SELECT RecordID, contactID, forename, surname, cliNumber, ranked_value
FROM ( SELECT RecordID, contactID, forename, surname,
cliNumber, ranked_value,
ROW_NUMBER() OVER
( PARTITION BY clinumber
ORDER BY ranked_value DESC, contactid DESC ) as ranking
FROM #MyData ) window
WHERE ranking = 1
April 30, 2015 at 11:06 am
Genius "MadAdmin"
It seems to work I think. I will do a lot of spotcheck from 1000s of dataset I have to see if this has worked.
For few sample data I have, it seems to be okay.
Many thanks.
Bij
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply