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 |
|
bigsinc
Starting Member
7 Posts |
 Posted - 2008-04-24 : 08:07:11
|
| Hi,I have data in several tables and Im having trouble filtering the data.This is statement that Im executing:1. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.SkillsThis is the statement that gives me the results I want:2. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastNameThe problem with this is I need to have Persons.Skills in the statement and DISTINCT doesnt filter the data the way I need it to because the data in Persons.Skills is different which results in I get duplicated results.Is it possible to do something like SELECT DISTINCT column1,..,column5,(SELECT Persons.Skills)by this I mean to apply distinct to some of the columns in the SELECT statement? |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-04-24 : 08:10:05
|
| if you want Skills in the result but there is more than 1 per person, you need to decide which Skill you want to selectEm |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-24 : 08:12:01
|
| Post your business rule on what skill record value you need to display for each person.First,latest or random? |
|
|
|
bigsinc
Starting Member
7 Posts |
Posted - 2008-04-24 : 08:24:38
|
| Thank you for your replies.Well the results Im getting is the followingUSA Microsoft Ben Stiller 5USA Microsoft Ben Stiller 4USA Microsoft Ben Stiller 2USA Microsoft Ben Stiller 1But I would like to haveUSA Microsoft Ben Stiller 5 (only one entry of Ben Stiller)The problem with Distinct is that it sees the the Persons.Skills are 5,4,3,2 and 1 .. therefore are not the same.Could you give me an example how I could do this if I would like to get the highest integer value? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-24 : 08:33:45
|
SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, MAX(PersonSkills.Skills)FROM YourTableGROUP BY Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName
E 12°55'05.25"N 56°04'39.16" |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-04-24 : 08:39:39
|
| bigsinc --Your problem is not coding or SQL related, it is that you have not clearly specified your requirements. Logically, returning a single row with "the skill" when the set of rows has multiple skills doesn't make sense.Please read this for more on that topic:http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspxOnce you clearly state exactly what you want, then we can help you write your code.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
bigsinc
Starting Member
7 Posts |
Posted - 2008-04-24 : 08:45:11
|
quote:
Originally posted by jsmith8858
bigsinc --Your problem is not coding or SQL related, it is that you have not clearly specified your requirements. Logically, returning a single row with "the skill" when the set of rows has multiple skills doesn't make sense.Please read this for more on that topic:http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspxOnce you clearly state exactly what you want, then we can help you write your code.- Jeffhttp://weblogs.sqlteam.com/JeffS
This helped, thank you! |
|
|
|
|
|
|
|
|
DISTINCT... help :)