September 15, 2009 at 9:13 am
I need to pull just one area code/phone # for each customer. The challenge I'm facing is that some customers have one areacode/phone#; others have two areacode/phone# and others don't have one at all.
I thought TOP might do it but it's not giving me the desired results. Maybe I'm not using it properly. Or is there another way?
Thx,
John
September 15, 2009 at 10:13 am
Hi, John. Please post the structure of the table that you are querying, some sample data and the query that you've used so far. Thanks.
September 15, 2009 at 11:18 am
You could perhaps do a row_number() over partition..... that would work.
-M
September 15, 2009 at 12:52 pm
Example for Row_Number Function
declare @t table (name nvarchar(10), tn nvarchar(10), date datetime)
insert into @t
select 'matt','1234567890','2009-09-15' union all
select 'john','2345678901','2009-05-15' union all
select 'pete','','' union all
select 'matt', '2345678901','2009-04-18' union all
select 'john','7890123456','2009-09-15'
select name, tn
from
(
select name, tn, row_number() over (partition by name order by date desc) as ranking
from @t
)
as ref
where ranking = 1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply