Home » SQL & PL/SQL » SQL & PL/SQL » SQL QUERY
SQL QUERY [message #200612] Tue, 31 October 2006 08:46 Go to next message
mgrazik
Messages: 3
Registered: October 2006
Junior Member
Hai everybody,
I am a beginner in Oracle.
I have a "student" table as follows

id name std div score
--- ---- --- --- -----
100 X 5 A 56
101 Y 5 A 48
102 X 5 A 64
103 Z 5 B 77
104 X 5 B 84
105 Z 5 B 57
106 X 6 A 45
107 Z 6 C 67

I want the query to display the details of standard 5 students having the same name and belonging to the same division? i.e.

id name std div score
--- ---- --- --- -----
100 X 5 A 56
102 X 5 A 64
103 Z 5 B 77
105 Z 5 B 57

Thanks in advance
Awaiting reply
Regards
Razik
Re: SQL QUERY [message #200616 is a reply to message #200612] Tue, 31 October 2006 09:04 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Select * from your table
where name||div in(
Select name||div from your table
group by name||div having count(*) >1);

This is one way I have done it in the past, i am sure others have a more efficient way to do this.
Re: SQL QUERY [message #200627 is a reply to message #200616] Tue, 31 October 2006 09:42 Go to previous messageGo to next message
mgrazik
Messages: 3
Registered: October 2006
Junior Member
It didn't get me the required result.
Thanks for the effort.
Re: SQL QUERY [message #200628 is a reply to message #200612] Tue, 31 October 2006 09:57 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
select id, name, std, div, score from (
	select a.*, count(*) over (partition by name, div order by name) cnt
	from test_student a
	where std = 5)
where cnt != 1
order by 1;
Re: SQL QUERY [message #200630 is a reply to message #200628] Tue, 31 October 2006 10:03 Go to previous message
mgrazik
Messages: 3
Registered: October 2006
Junior Member
Got the required output.
Thank you very much.
Previous Topic: 'Dynamic' Records with Dynamic Cursors
Next Topic: Union Vs Union All
Goto Forum:
  


Current Time: Sat Dec 10 05:00:35 CST 2016

Total time taken to generate the page: 0.11797 seconds