February 7, 2011 at 8:45 am
I am having a problem coming up with the correct select statement.
Here is my sample data:
ID,NAME,PHONE
1,user1,5555551234
2,user1,5555552222
3,user1,1235554444
4,user2,1234567890
5,user2,3211235555
6,user3,9991234567
7,user3,0005551111
8,user3,6548971234
Now, I am trying to join table to another table, based on the NAME column. And my final data set then has multiple rows for each NAME, because of the multiple phone #'s for each NAME.
I only want one number for each NAME, doesn't matter which. I want my data set for this table to be rows ID 1,4,&6 plus the corersponding NAME and PHONE; and ignore the rest of the data rows. I tried using the MIN() or MAX() functions on the ID column, but then it I get told that NAME or PHONE are not part of an aggregate function.
What do you guys suggest?
February 7, 2011 at 8:52 am
Use a CTE and one of the ranking functions to limit the set, then join to that.
;with Phones (Name, Phone, Row) as
(Select Name, Phone, Row_Number() over (Partition by Name Order by ID)
from MyPhonesTable)
select *
from MyOtherTable
left outer join Phones
on MyOtherTable.Name = Phones.Name
and Phones.Row = 1;
This assumes you're actually using SQL 2008 (or at least 2005), based on the forum you posted in. If it's actually 2000 or earlier, there's a different solution using a derived table and the Min function, which works but usually doesn't perform as well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 9:10 am
A slightly different way, and in most respects equivalent to GSquared's answer
CREATE TABLE #T(ID INT,aNAME VARCHAR(20),PHONE VARCHAR(15))
INSERT INTO #T
SELECT 1,'user1','5555551234' UNION ALL
SELECT 2,'user1','5555552222' UNION ALL
SELECT 3,'user1','1235554444' UNION ALL
SELECT 4,'user2','1234567890' UNION ALL
SELECT 5,'user2','3211235555' UNION ALL
SELECT 5,'user2','3211235555' UNION ALL
SELECT 5,'user2','3211235555' UNION ALL
SELECT 6,'user3','9991234567' UNION ALL
SELECT 7,'user3','0005551111' UNION ALL
SELECT 8,'user3','6548971234'
;with cte
as (select row_number() over(partition by aName order by ID) as rn,
Id,aName,Phone
from #T)
SELECT * FROM cte WHERE rn = 1
Results:
rnIdaNamePhone
11user15555551234
14user21234567890
16user39991234567
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply