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 |
|
man.olie
Starting Member
1 Post |
 Posted - 2010-09-09 : 11:00:55
|
| First I want you to know that I am very grateful about your effort.OK here it is:Take this simple result set (a complex query thought with column/table aliases and several joins) that I have:ColAlias1 ColAlias2 ColAlias3 1 EE Text1 2 EE Text23 EE Text31 BB Text42 BB Text53 BB Text64 BB Text73 GG Text84 GG Text9....and so onSimple, right???Now I just want to get the rows which have different ColAlias2 values.I don't care about the occurance order (it might be the first, the second, I don't care)For example (first occurance of current order):CA1 CA2 CA31 EE Text11 BB Text43 GG Text8Any ideas? |
|
|
MSquared
Yak Posting Veteran
52 Posts |
Posted - 2010-09-09 : 11:18:09
|
| I think this might be what you are looking fordeclare @t table (ColAlias1 smallint, ColAlias2 varchar(2),ColAlias3 varchar(5))insert into @tselect 1, 'EE', 'Text1' Union allselect 2, 'EE', 'Text2' Union allselect 3, 'EE', 'Text3' Union allselect 1, 'BB', 'Text4' Union allselect 2, 'BB', 'Text5' Union allselect 3, 'BB', 'Text6' Union allselect 4, 'BB', 'Text7' Union allselect 3, 'GG', 'Text8' Union allselect 4, 'GG', 'Text9' union allselect 5, 'WW', 'Text0'select t.ColAlias1, t.ColAlias2, t.ColAlias3from @t tinner join (select ColAlias1, COUNT(distinct ColAlias2) NumRecords from @t group by ColAlias1 having COUNT(distinct ColAlias2) > 1) v on t.ColAlias1 = v.ColAlias1For Faster results please follow the posting guidelines herehttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
|
|
|
|
|
Simple Distinction Question / Complex Answer?