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.


Forums › Off topic › SQL Help
ajobbins

5053 posts

Uber Geek
+1 received by user: 1279

Trusted

#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
chiefie
I iz your trusted friend
5877 posts

Uber Geek
+1 received by user: 151

Retired Mod
Trusted
Lifetime subscriber

  #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

 



barf
643 posts

Ultimate Geek


  #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

chiefie
I iz your trusted friend
5877 posts

Uber Geek
+1 received by user: 151

Retired Mod
Trusted
Lifetime subscriber

  #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

 



chiefie
I iz your trusted friend
5877 posts

Uber Geek
+1 received by user: 151

Retired Mod
Trusted
Lifetime subscriber

  #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

 

barf
643 posts

Ultimate Geek


  #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

ajobbins

5053 posts

Uber Geek
+1 received by user: 1279

Trusted

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

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




Twitter: ajobbins

 
 
 
 

Shop on-line at New World now for your groceries (affiliate link).
YoungKiwi
2 posts

Wannabe Geek


  #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

chiefie
I iz your trusted friend
5877 posts

Uber Geek
+1 received by user: 151

Retired Mod
Trusted
Lifetime subscriber

  #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

 

YoungKiwi
2 posts

Wannabe Geek


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

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

Create new topic








Geekzone Live »

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


Updates »

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.








RSS feeds
Main feed
Forums feed
Copyright
©2002-2026 Geekzone®
Site features
Geekzone BI dashboard
Geekzone Badges
Geekzone Status Page

 

Site Information
Subscribe to Geekzone
Privacy Statement
Forum Usage Guidelines (FUG)
Advertising
Trademark and copyright