Multiple Occurrences - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Occurrences

Author

Topic

dabeyo
Starting Member

2 Posts

Posted - 2007-06-19 : 10:21:17

If I want to return records where a column has similar data and occurs more than once what is the function?

Ex. If a database contains:
Column 1 Column 2 Column 3
1 Dog White
2 Cat Brown
3 Dog Black
4 Mouse Black
5 Cat White

and I want to show all records where the 2nd column has more than one occurrences so that I get both records where Dog appeared and both records where Cat appeared (since each appeared more than 1 time) - what do i need to write as my function?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-19 : 10:24:05

read about group by and having clauses.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

dabeyo
Starting Member

2 Posts

Posted - 2007-06-19 : 11:29:24

Can anyone help abit further?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-19 : 11:53:22

The JOIN is usually the most efficient but try them all, check the query plans and take your pick.

SELECT T.*
FROM YourTable T
    JOIN (
            SELECT T1.Column2
            FROM YourTable T1
            GROUP BY T1.Column2
            HAVING COUNT(*) > 1
        ) D
        ON T.Column2 = D.Column2

SELECT *
FROM YourTable T
WHERE EXISTS (
        SELECT *
        FROM YourTable T1
        WHERE T1.Column2 = T.Column2
        GROUP BY T1.Column2
        HAVING COUNT(*) > 1
    )

SELECT *
FROM YourTable T
WHERE T.Column2 IN (
        SELECT T1.Column2
        FROM YourTable T1
        GROUP BY T1.Column2
        HAVING COUNT(*) > 1
    )

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-20 : 08:25:28

Also Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources