Home » SQL & PL/SQL » SQL & PL/SQL » selecting result of row that occurs more than 1.
selecting result of row that occurs more than 1. [message #299070] Fri, 08 February 2008 18:32 Go to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member
Hi there. I have got this table:
Table customer.
CUSTOMER_ID SU
----------- --
        100 CA
        101 CA
        102 CA
        104 CA
        106 CA
        108 MN
        201 CA
        201 MA
        201 NY
        202 MA
        204 TX

CUSTOMER_ID SU
----------- --
        205 NY
        206 NY
        207 MA
        214 TX
        215 TX
        221 TX
        222 CA
        222 TX
        224 TX
        226 NY
        227 MA

CUSTOMER_ID SU
----------- --
        228 NY
        229 CA
        230 MA
        231 MA
        232 TX
        233 TX
        234 TX
        235 TX


If you see there, 201, 222 occurs more than once. How do I show those two results only? As in final result:
CUSTOMER_ID SU
----------- --
        201 CA
        201 MA
        201 NY
        222 CA
        222 TX
Re: selecting result of row that occurs more than 1. [message #299071 is a reply to message #299070] Fri, 08 February 2008 18:46 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
HAVING COUNT(SU) > 1
Re: selecting result of row that occurs more than 1. [message #299074 is a reply to message #299070] Fri, 08 February 2008 20:18 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Perhaps you meant HAVING count(customer_id) ... ?

as in ..

SELECT c.customer_id,
  c.su
FROM customer c
WHERE c.customer_id IN
  (SELECT c2.customer_id
   FROM customer c2
   GROUP BY c2.customer_id HAVING COUNT(c2.customer_id) > 1)
Re: selecting result of row that occurs more than 1. [message #299121 is a reply to message #299074] Sat, 09 February 2008 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't give solution in Newbie forum, give hint/clue to help them find the solution and so learn.

Regards
Michel
Re: selecting result of row that occurs more than 1. [message #299124 is a reply to message #299070] Sat, 09 February 2008 13:32 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Understood.

However, assuming that this not just a personal preference of yours, and is instead indicative of the general ethos here on the forums, then might I suggest that you reflect this position in the posting guidelines.

Reading back, I see lots of answers to questions in the Newbie forum, so I hope you can appreciate my confusion regarding the SOP here on orafaq.com.

As an aside, although I only joined these forums in the last couple of days, I've been a member of many forum communities, and whilst I can appreciate the value of rules and guidelines etc, I equally appreciate the value of trying to make a set of forums a welcoming place where people want to hang-out and help each other out. Double posting the same message like that in two different threads, rebuking someone who's new to your community, and is obviously just trying to get involved by answering a few questions, is a great way to further cultivate the "harsh" tone that seems prevalent here. (For the record, as a mod on previous forums, I'd probably have opted for a quiet PM to the offending party.)

Anyhoo, thats just my two cents. I'll get back in my box now Smile

Regards,
Michael

[Updated on: Sat, 09 February 2008 14:06]

Report message to a moderator

Re: selecting result of row that occurs more than 1. [message #299127 is a reply to message #299124] Sat, 09 February 2008 16:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hi Michael,

Welcome to the forum. Hope you want to stick around a bit; the more the merrier Smile
You are right about the fact that there's nothing in the forum-guidelines about giving out solutioms; it's become more like a 'culture' if you can call it that, to try to get newbies to find the actual solution themselves, rather than spoonfeed the answer.
As such it is not a rule that we don't give out solutions, we try to discourage it.
I don't think Michel meant anything bad with his posts; he tends to use some standard templates Wink

Please keep contributing to the forums!
Re: selecting result of row that occurs more than 1. [message #299134 is a reply to message #299124] Sat, 09 February 2008 18:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
moshea wrote on Sat, 09 February 2008 11:32

... might I suggest that you reflect this position in the posting guidelines ...



Good idea. The forum guide was aimed primarily at people posting questions rather than people posting answers. We discussed this amongst ourselves and it was added to the moderator guide, but we overlooked the forum guide. After reading your response, I just added the following section "Responding to Posts" to the forum guide, subject to edits by other moderators:

When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usually best to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, rather than providing complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


moshea wrote on Sat, 09 February 2008 11:32

Reading back, I see lots of answers to questions in the Newbie forum, so I hope you can appreciate my confusion regarding the SOP here on orafaq.com.

... I'd probably have opted for a quiet PM to the offending party. ...



If one moderator sent a PM, then the others would not know about it and you might get one from every moderator and others would look at this post and assume it was O.K. to do the same. I assure you, you were not singled out. You will find many similar responses, but some slip past us. We're doing the best we can.


Re: selecting result of row that occurs more than 1. [message #299140 is a reply to message #299070] Sun, 10 February 2008 00:14 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Guys,

Thanks for the feedback.

Apologies if my post was construed as a criticism of the amazing work yee'r doing here. The more I read, the more I realise what an amazing resource the orafaq.com/forums are.

I'll try and adopt the appropriate zen master approach in future when posting in the Newb/Homework forums. (which may be difficult for me, as I have a long way to go to achieve Michel-like Guru-ness Smile)

Cheers,
Michael.
Re: selecting result of row that occurs more than 1. [message #300663 is a reply to message #299070] Sun, 17 February 2008 11:25 Go to previous messageGo to next message
grb.prasad@gmail.com
Messages: 1
Registered: February 2008
Location: HYDERABAD
Junior Member
Select distinct(customer_id) from tablename;
Re: selecting result of row that occurs more than 1. [message #300664 is a reply to message #300663] Sun, 17 February 2008 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How does this query give the customer ids that are repeated?

Regards
Michel
Re: selecting result of row that occurs more than 1. [message #300665 is a reply to message #300663] Sun, 17 February 2008 12:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
grb.prasad@gmail.com wrote on Sun, 17 February 2008 09:25
Select distinct(customer_id) from tablename;


Wrong. That does not produce the requested result. A correct solution has already been given. Please test your code prior to posting.

Looks like Michel beat me by 1 minute or a fraction thereof.

[Updated on: Sun, 17 February 2008 12:25]

Report message to a moderator

Re: selecting result of row that occurs more than 1. [message #300882 is a reply to message #300665] Mon, 18 February 2008 07:58 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Barbara Boehmer wrote on Sun, 17 February 2008 13:24
grb.prasad@gmail.com wrote on Sun, 17 February 2008 09:25
Select distinct(customer_id) from tablename;


Wrong. That does not produce the requested result. A correct solution has already been given. Please test your code prior to posting.

Looks like Michel beat me by 1 minute or a fraction thereof.


Somewhere between 1 second and 1 minute and 59 seconds (I'm giddy today - never had to work on President's Day before). Maybe we should have seconds here.
Previous Topic: Package Specification
Next Topic: with <dummy_name> as ..
Goto Forum:
  


Current Time: Wed Dec 07 18:42:03 CST 2016

Total time taken to generate the page: 0.05760 seconds