May 28, 2003 at 5:08 pm
Before data rows:
a3
a1
a7
b3
b7
c9
After data rows:
a3
b3
c9
Essentially I want to GROUP the data by column 1 and remove those records having "count(c1)> 1" but
select c1,c2
from table
group by c1, c2
having count(c1) = 1
sure deosn't work. Guidance requested!
Bill
May 28, 2003 at 5:25 pm
select c1, count(*)
from table
group by c1
having count(*) > 1
If this is on the wrong track, explain the 'before data rows:' and 'after data rows' part.
Michelle
May 28, 2003 at 6:16 pm
"Essentially I want to GROUP the data by column 1 and remove those records having "count(c1)> 1"
Question : What is the sequencing criteria or is it just a heap. I think some 'guru' advised previously never makes an assumption about the sequence in which the data will be returned.
Maybe this will help (quick and dirty):
Create Table Test(c1 char(1),c2 int)
Go
Insert Test values('a', 3)
Insert Test values('a', 1)
Insert Test values('a', 7)
Insert Test values('b', 3)
Insert Test values('b', 7)
Insert Test values('c', 9)
Go
Create Table #Test(Seq Int identity(1,1),c1 char(1),c2 int)
Go
Insert #Test
Select c1,t.c2
From Test as t
GO
Select t.c1,t.c2
From #Test as t
Where t.seq=(Select Min(tt.Seq) from #test as tt Where tt.c1=t.c1)
Go
drop table test
drop table #test
May 28, 2003 at 9:27 pm
Mimorr,
When I run
select c1, count(*)
from table
group by c1
having count(*) > 1
I get essentially what I want BUT only column 1 (c1) is returned in the resultset.
I want all fields in the table returned in the resultset. So I am forced to include both fields c1 and c2 (or *) in the select statement and that forces me to include both fields in the GROUP BY statement. But both
select c1, c2, count(*)
from table1
group by c1, c2
having count(c1)> 1
and
select *, count(*)
from table1
group by c1, c2
having count(c1) >1
return empty result sets. Puzzling.
Bill
May 29, 2003 at 7:32 am
I'm not sure I understand exactly what you want.
Do you want to return on one record for each distinct value of c1? Meaning either one of these sets:
a 3
b 3
c 9
or
a 1
b 3
c 9
or
a 7
b 3
c 9
or etc, etc, etc....
If this is the case which set do you want returned? The first row for c1 for each value of c1 Meaning:
a 3
b 3
c 9
Or do you want to return all the records for a and b since a and b have more that 1 record? Meaning:
a 3
a 1
a 7
b 3
b 7
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
May 29, 2003 at 7:38 am
here you go:
select table.c1, table.c2, table.c3, dt.count_c1
from table inner join
(
select c1, count(*) as count_c1
from table
group by c1
having count(*) > 1
) as dt on table.c1 = dt.c1
May 29, 2003 at 7:53 am
How about
select t.c1,t.c2
from test t
inner join (
select c1, count(*) as num
from test
group by c1
having count(*) > 1) as a on a.c1 = t.c1
left outer join (
select c1, min(c2) as c2
from test
group by c1) as b on b.c1 = t.c1 and b.c2 = t.c2
where b.c1 IS NULL
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply