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.


ajobbins

Awesome
4891 posts

Uber Geek

Trusted
Subscriber

#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
5854 posts

Uber Geek

Mod Emeritus
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
5854 posts

Uber Geek

Mod Emeritus
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
5854 posts

Uber Geek

Mod Emeritus
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

Awesome
4891 posts

Uber Geek

Trusted
Subscriber

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

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




Twitter: ajobbins


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
5854 posts

Uber Geek

Mod Emeritus
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





News »

Nanoleaf enhances lighting line with launch of Triangles and Mini Triangles
Posted 17-Oct-2020 20:18


Synology unveils DS1621+ 
Posted 17-Oct-2020 20:12


Ingram Micro introduces FootfallCam to New Zealand channel
Posted 17-Oct-2020 20:06


Dropbox adopts Virtual First working policy
Posted 17-Oct-2020 19:47


OPPO announces Reno4 Series 5G line-up in NZ
Posted 16-Oct-2020 08:52


Microsoft Highway to a Hundred expands to Asia Pacific
Posted 14-Oct-2020 09:34


Spark turns on 5G in Auckland
Posted 14-Oct-2020 09:29


AMD Launches AMD Ryzen 5000 Series Desktop Processors
Posted 9-Oct-2020 10:13


Teletrac Navman launches integrated multi-camera solution for transport and logistics industry
Posted 8-Oct-2020 10:57


Farmside hits 10,000 RBI customers
Posted 7-Oct-2020 15:32


NordVPN starts deploying colocated servers
Posted 7-Oct-2020 09:00


Google introduces Nest Wifi routers in New Zealand
Posted 7-Oct-2020 05:00


Orcon to bundle Google Nest Wifi router with new accounts
Posted 7-Oct-2020 05:00


Epay and Centrapay partner to create digital gift cards
Posted 2-Oct-2020 17:34


Inseego launches 5G MiFi M2000 mobile hotspot
Posted 2-Oct-2020 14:53









Geekzone Live »

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


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



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.