Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


Awesome
4805 posts

Uber Geek
+1 received by user: 1061

Trusted
Subscriber

Topic # 11558 30-Jan-2007 19:32
Send private message

Hey guys, can anyone tell me how with MSSQL I can pull out all records where a particular ID appears in more than one record?

I.E. say I have 10 member IDs,


1111
1112
1113
1114
1115
1116
1111
1112
1113
1114

So I would want a query that would return the results:

1111
1112
1113
1114

as these appear in more than one record.




Twitter: ajobbins


Create new topic
I iz your trusted friend
5794 posts

Uber Geek
+1 received by user: 138

Mod Emeritus
Trusted
Lifetime subscriber

  Reply # 59277 30-Jan-2007 20:46
Send private message

try with this SQL string:

SELECT vnum
FROM tes
GROUP BY vnum
HAVING COUNT( vnum ) >1


vnum is the column where your value like 1111, 1112, 1113..... the HAVING is for checking the condition which we want to compare with. In this case, we are selecting [SELECT] values [vnum] that occurred more than once [HAVING COUNT(vnum)>1], which we [GROUP BY vnum] from the table [tes]




Internet is my backyard...

 

«Geekzone blog: Tech 'n Chips Takeaway» «Personal blog: And then...»

 

Please read the Geekzone's FUG

 


643 posts

Ultimate Geek


  Reply # 59279 30-Jan-2007 20:50

in MySQL you can just append UNIQUE to the end of the query




Sniffing the glue holding the Internet together

I iz your trusted friend
5794 posts

Uber Geek
+1 received by user: 138

Mod Emeritus
Trusted
Lifetime subscriber

  Reply # 59280 30-Jan-2007 20:59
Send private message

barf: in MySQL you can just append UNIQUE to the end of the query

He said he is using MSSQL.

My suggestion is only using ANSI SQL, so it is not vendor/product specific.




Internet is my backyard...

 

«Geekzone blog: Tech 'n Chips Takeaway» «Personal blog: And then...»

 

Please read the Geekzone's FUG

 


I iz your trusted friend
5794 posts

Uber Geek
+1 received by user: 138

Mod Emeritus
Trusted
Lifetime subscriber

  Reply # 59281 30-Jan-2007 21:05
Send private message

SELECT vnum, count( vnum ) 
FROM tes
GROUP BY vnum
HAVING COUNT( vnum ) >1


added [,COUNT(vnum)] if you want to see the number of occurrence.





Internet is my backyard...

 

«Geekzone blog: Tech 'n Chips Takeaway» «Personal blog: And then...»

 

Please read the Geekzone's FUG

 


643 posts

Ultimate Geek


  Reply # 59282 30-Jan-2007 21:09

I just thought that might have been portable syntax but I guess it isn't.




Sniffing the glue holding the Internet together



Awesome
4805 posts

Uber Geek
+1 received by user: 1061

Trusted
Subscriber

  Reply # 59352 31-Jan-2007 11:56
Send private message

Thanks Chiefie, works perfectly. Exactly what I was looking for




Twitter: ajobbins


2 posts

Wannabe Geek


  Reply # 61439 21-Feb-2007 13:42
Send private message

Your situation is resolved now, but a query that is less processor hungry is :

select distinct vnum from table

I iz your trusted friend
5794 posts

Uber Geek
+1 received by user: 138

Mod Emeritus
Trusted
Lifetime subscriber

  Reply # 61442 21-Feb-2007 13:45
Send private message

YoungKiwi: Your situation is resolved now, but a query that is less processor hungry is :

select distinct vnum from table


That will also return 1115 and 1116 which is not what the OP wanted. The OP wanted to return only values that appeared 2 or more times.




Internet is my backyard...

 

«Geekzone blog: Tech 'n Chips Takeaway» «Personal blog: And then...»

 

Please read the Geekzone's FUG

 


2 posts

Wannabe Geek


Reply # 61462 21-Feb-2007 16:48
Send private message

oops, input failure at read stage.... Tongue out

Create new topic

Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

Intel introduces new NUC kits and NUC mini PCs
Posted 16-Aug-2018 11:03


The Warehouse leaps into the AI future with Google
Posted 15-Aug-2018 17:56


Targus set sights on enterprise and consumer growth in New Zealand
Posted 13-Aug-2018 13:47


Huawei to distribute nova 3i in New Zealand
Posted 9-Aug-2018 16:23


Home robot Vector to be available in New Zealand stores
Posted 9-Aug-2018 14:47


Panasonic announces new 2018 OLED TV line up
Posted 7-Aug-2018 16:38


Kordia completes first live 4K TV broadcast
Posted 1-Aug-2018 13:00


Schools get safer and smarter internet with Managed Network Upgrade
Posted 30-Jul-2018 20:01


DNC wants a safer .nz in the coming year
Posted 26-Jul-2018 16:08


Auldhouse becomes an AWS Authorised Training Delivery Partner in New Zealand
Posted 26-Jul-2018 15:55


Rakuten Kobo launches Kobo Clara HD entry level reader
Posted 26-Jul-2018 15:44


Kiwi team reaches semi-finals at the Microsoft Imagine Cup
Posted 26-Jul-2018 15:38


KidsCan App to Help Kiwi Children in Need
Posted 26-Jul-2018 15:32


FUJIFILM announces new high-performance lenses
Posted 24-Jul-2018 14:57


New FUJIFILM XF10 introduces square mode for Instagram sharing
Posted 24-Jul-2018 14:44



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.