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 |
|
sford
Starting Member
4 Posts |
 Posted - 2007-04-06 : 17:49:17
|
| Is there some way to use the distinct keyword so that it applies ONLY to a subset of the items in the select list???For example, suppose I want toselect col_1, col_2, col_3, col_4but I do not want distinct applied against all 4 items...Maybe I want all 4 items in the selection list,but I want distinct to use only col_3 as its filtration criteria...I know the syntax shown below is not valid, but I am showing it anyway because I am hoping it will illustrate what I am looking for...Is there a VALID syntax that is something like this???select col_1, col_2, (distinct col_3), col_4orselect col_1, col_2, distinct (col_3), col_4 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2007-04-06 : 18:38:09
|
| If you can provide a sample input, and the desired output from that query,it will help both us (and You) to understand what you are trying to achieve.rockmoose |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-06 : 18:38:14
|
| To do this, you would use a GROUP BY.But please show us a data example so that we can help you with the code.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sford
Starting Member
4 Posts |
Posted - 2007-04-06 : 20:03:55
|
| Hypothetical data set# | Name | Comp | Rela----------------------1 | Stev | ABCD | OOOO2 | Joan | efgh | ZZZZ3 | Arlo | 1234 | ZZZZ4 | Arlo | 1234 | XXXX5 | Mike | ZYXW | ZZZZDesired ResultName | Comp | Rela------------------Stev | ABCD | OOOOJoan | efgh | ZZZZArlo | 1234 | ZZZZMike | ZYXW | ZZZZEqually Desirable ResultName | Comp | Rela------------------Stev | ABCD | OOOOJoan | efgh | ZZZZArlo | 1234 | XXXXMike | ZYXW | ZZZZIf I do this...Select distinct Name, CompI get this...Name | Comp-----------Stev | ABCDJoan | efghArlo | 1234Mike | ZYXWThis is limited to just the records I want, but does not include the Rela fieldOn the other hand, If I do this...Select distinct Name, Comp, RelaI get this...Name | Comp | Rela------------------Stev | ABCD | OOOOJoan | efgh | ZZZZArlo | 1234 | ZZZZArlo | 1234 | XXXXMike | ZYXW | ZZZZThis has the Rela field, but it has the Arlo record duplicated...I want all three fields, but I want only ONE Arlo record... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-06 : 22:45:47
|
[code]select Name, Comp, max(Rela) -- or min()from tablegroup by Name, Comp[/code]
KH |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Using DISTINCT