Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DJS051085
Starting Member
3 Posts |
 Posted - 2015-03-05 : 15:40:50
|
| I have two tables, "People" - which contains two columns "first_name" and "last_name"- and "Info" - which contains two columns "tagtype" and "myinfo". Both tables have a third column, "myid" with key ID that IS unique on the poeple table but is not necessarily unique for the Info table.The "People" table is pretty obviously names of people. The Info table contains different information items for the people - e.g. tagtype=phone, myinfo=867.5309 . Some people have multiple phone numbers listed. I want to list ONE phone number per person. I thought this would do the trick:SELECT DISTINCT People.myid, People.first_name, People.last_name, Info.tagtype, Info.myinfo FROM People JOIN Info On People.myid=Info.myid WHERE tagtype="Phone"but this does not work. I want to list each distinct myid (each distinct person) followed by the first name, last name, and one of their phone numbers. But the command I used lists each person multiple times if they have multiple phone numbers.Any ideas of how to get the query I want? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-05 : 15:50:43
|
| Which one do you want to show? You can use the ROW_NUMBER() function to number them and then only grab the one where it equals 1 by using a CTE.Look at example B for the CTE version: https://msdn.microsoft.com/en-us/library/ms186734.aspxYou'll need to incorporate PARTITION BY. Look at example C.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2015-03-10 : 03:49:50
|
| Alternatively Group by other columns and select minimum or maximum phone numberMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
SELECT DISTINCT