Problem with Select - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with Select

Author

Topic

Faiyth
Starting Member

19 Posts

Posted - 2002-05-01 : 14:28:50

In a column called Numbers in a table called MyRecords I have a list of a bunch of records listed here. Now, I only want to return the records that are listed twice. Not the other ones.. so heres some sample data..

Numbers
1
2
2
3
3
4
5
6

I want to return ONLY the records that are listed twice (such as the number 2 and 3 records, as they are listed twice) How do I do this? I've tried count but it's not working.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-01 : 14:33:23


select
    numbers
from
    MyRecords
group by
    numbers
having
    count(*) = 2

<O>

Edited by - Page47 on 05/01/2002 14:38:01

Faiyth
Starting Member

19 Posts

Posted - 2002-05-01 : 14:41:37

Now their grouped. I don't want them grouped. Each has different information and I need to return all duplicate records, they can't be grouped.

So my results would be:
2
2
3
3

How is that done?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-01 : 14:43:08

try this . . .


select
    a.numbers
from
    MyRecords a
where
   (select
        count(*)
    from
        #myRecords b
    where
        a.numbers = b.numbers) = 2

...or...


select
    <col list>
from
    MyRecords
where
    numbers in (
    select
        numbers
    from
        #myrecords
    group by
        numbers
    having
        count(*) = 2 )

<O>

Edited by - Page47 on 05/01/2002 14:45:39

Faiyth
Starting Member

19 Posts

Posted - 2002-05-01 : 16:09:24

Thank you so much! That was exactly want I wanted. Can't thank you enough.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources