Home » SQL & PL/SQL » SQL & PL/SQL » Idea pls
Idea pls [message #194454] Fri, 22 September 2006 05:23 Go to next message
irajasekharhexa
Messages: 6
Registered: June 2005
Location: Bangalore
Junior Member

I have a table in where i need to find out the values which are
repeated more than once at a column.

Say table 'a' contains columns a1,b1

in a1 column some of the values are repeated i want to find out those values

Any help in this?
Re: Idea pls [message #194462 is a reply to message #194454] Fri, 22 September 2006 05:32 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


 use the count function.


regards,
icon2.gif  Re: Idea pls [message #194463 is a reply to message #194454] Fri, 22 September 2006 05:34 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
since you have not given the script you leave no choice.search for rowid in this forum.
Re: Idea pls [message #194476 is a reply to message #194454] Fri, 22 September 2006 06:03 Go to previous messageGo to next message
chimpychimpy
Messages: 7
Registered: September 2006
Junior Member
irajasekharhexa wrote on Fri, 22 September 2006 05:23

I have a table in where i need to find out the values which are
repeated more than once at a column.

Say table 'a' contains columns a1,b1

in a1 column some of the values are repeated i want to find out those values

Any help in this?

select a1 from a
   group by a1
   having count(*) > 1 

-- having is group by's equivalent to 'where' - its how you condition a group
Re: Idea pls [message #194477 is a reply to message #194454] Fri, 22 September 2006 06:04 Go to previous messageGo to next message
irajasekharhexa
Messages: 6
Registered: June 2005
Location: Bangalore
Junior Member

See my query is suppose if i have a table like
a1 b1
xxx 1
yyyy 2
aaa 3
cccc 1

I need to get output like

1,xxx,cccc

Means for the same id how many different related records are present.


I think i explained what i supposed to do.


Thanks in advance

Re: Idea pls [message #194478 is a reply to message #194477] Fri, 22 September 2006 06:13 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
select * from tablename where rowid<2
Re: Idea pls [message #194479 is a reply to message #194477] Fri, 22 September 2006 06:13 Go to previous messageGo to next message
chimpychimpy
Messages: 7
Registered: September 2006
Junior Member
then use the previous as a sub-query

select a1,b1 from a 

where a1 in (  -- subquery from before
    select a1 from a 
    group by a1
    having count(*) > 1
)

Re: Idea pls [message #194480 is a reply to message #194479] Fri, 22 September 2006 06:15 Go to previous message
chimpychimpy
Messages: 7
Registered: September 2006
Junior Member
out of interest, which company do you work for in bangalore?
Previous Topic: ORA Error
Next Topic: What happens to data in case of improper shutdown if we have not committed it.
Goto Forum:
  


Current Time: Tue Dec 06 08:08:04 CST 2016

Total time taken to generate the page: 0.09935 seconds