SQL Syntax - group by and having count

[mashedpotatohead]

Does anyone have any recommendations on how to solve the following?
I would like to have a query that selects ALL columns from a database,
however only records that have a count of a certain column which is
greater than 1 when the results are grouped by a few columns. I know
the following query doesnt work (because it contains items in the
SELECT that arent in the GROUP BY), but its the jist of what I need to
do.

select a,b,c,d,e,f,g,h
from table1
group by a,b,c,d
having count(e) > 1

Can anyone help me out with this?

[David Portas]

Re: SQL Syntax - group by and having count

Below is just a guess because your spec isn't very precise. The
following article explains the best way to get help with this sort of
problem:

http://www.aspfaq.com/etiquette.asp?id=5006

Meanwhile, try this:

SELECT a,b,c,d,e,f,g,h
FROM Table1 AS T
WHERE EXISTS
(SELECT *
FROM Table1
WHERE a = T.a
AND b = T.b
AND c = T.c
AND d = T.d
AND e <> T.e)

--
David Portas
SQL Server MVP
--

[mashedpotatohead]

Re: SQL Syntax - group by and having count

Thanks for your speedy response. Sorry I was unclear in my question.
Hopefully the sample data will help with my explanation.

example data from table (the real table has 300K records)
a b c d e f g, h......
abcd 547 90 206 19126 02385
abce 547 90 207 19127 9872349
abce 547 90 207 79823 78923075
abce 547 90 207 79823 79872309
abce 547 90 207 79823 89723534
abce 547 90 208 79823 72983454
abce 547 90 208 77834 89052256
abcf 548 91 208 77834 89437545

desired results
abce 547 90 207 79823 78923075
abce 547 90 207 79823 79872309
abce 547 90 207 79823 89723534

[mashedpotatohead]

Re: SQL Syntax - group by and having count

In case anyone else is looking for a similar sulution, a user from
another forum posted this answer which helped me out greatly.

select a.* from <tablename> a join
(select col_a,col_b,col _c,col_d,col_e, count(*) dup_count from
<tablename>
group by col_a,col_b,col _c,col_d,col_e
having count(*)>1) b
on a.col_a=b.col_a and
a.col_b=b.col_b and
a.col_c=b.col_c and
a.col_d=b.col_d and
a.col_e=b.col_e