Easy way to do this?

[james0816]

I have a table with over 5000 records in it. I would like to have a query that would only display those records that have more than one record on Field1.

for example:

Field1:

one
two
two
three
four
five
five


Result set would be:

two
two
five
five


Can someone assist me on this?

Thx

 

[r937]

the following query gets you all the field1 values that occur more than once --

select field1 
  from yourtable
group by field1
having count(*) > 1

if you now need to see the entire rows that those values are in, you can try this --

select foo, bar, field1
  from yourtable
 where field1 in
     ( select field1 
         from yourtable
       group by field1
       having count(*) > 1 )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[TonyGroves]

How about:
[tt]
SELECT fld1
FROM tbl1
WHERE
fld1
IN
(
SELECT fld1
FROM tbl1
GROUP BY fld1
HAVING count(*)>1
)
ORDER BY fld1
[/tt]