Home » SQL & PL/SQL » SQL & PL/SQL » Query
Query [message #217063] Wed, 31 January 2007 09:26 Go to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Hi,

I have cusustomer table and contains different department, but i need first 5 to record for each dedpartment.
Re: Query [message #217064 is a reply to message #217063] Wed, 31 January 2007 09:30 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi Arvind,
What exactly your question please be clear ?
Quote:
but i need first 5 to record for each dedpartment.

Ashu
Re: Query [message #217065 is a reply to message #217064] Wed, 31 January 2007 09:36 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Customer table
Name dept sal
a it 1000
b Network 2000
c it 1001
d Network 2001
c it 100
d Network 200
e it 10
f Network 20


Output
a it 1000
b Network 2000
c it 1001
d Network 2001

Note:- I need first two record for each department, please help


Re: Query [message #217066 is a reply to message #217063] Wed, 31 January 2007 09:40 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
drop table customer;
create table customer (name varchar2(10), dept varchar2(10), sal number);
insert into customer values ('a','it',1000);
insert into customer values ('b','Network',2000); 
insert into customer values ('c','it',1001);
insert into customer values ('d','Network',2001); 
insert into customer values ('c','it',100);
insert into customer values ('d','Network',200);
insert into customer values ('e','it',10);
insert into customer values ('f','Network',20);
commit;

SELECT *
  FROM (SELECT name,
               dept,
               sal,
               ROW_NUMBER() OVER(PARTITION BY dept order by name) v
          FROM customer)
 WHERE v <= 2


The order of result is a bit different though..
        NAME	DEPT	SAL	V
	b	Network	2000	1
	d	Network	2001	2
	a	it	1000	1
	c	it	1001	2

[Updated on: Wed, 31 January 2007 09:50]

Report message to a moderator

Re: Query [message #217119 is a reply to message #217065] Wed, 31 January 2007 14:09 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And how do you define "first two in each department?" I'm claiming that E is first and C is second in the "it" department.
Re: Query [message #217167 is a reply to message #217066] Wed, 31 January 2007 22:12 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Thanks

Working fine
Re: Query [message #217239 is a reply to message #217167] Thu, 01 February 2007 05:24 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
This is the order of records when I query them using:
select * from customer;

  	NAME	DEPT	SAL
 	a	it	1000
	b	Network	2000
	c	it	1001
	d	Network	2001
	c	it	100
	d	Network	200
	e	it	10
	f	Network	20


So I guess it shows 'a' is first and 'c' is second in 'it' dept.
Re: Query [message #217240 is a reply to message #217239] Thu, 01 February 2007 05:26 Go to previous message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
yes

But problem has been resolved with the help of Over and Patition by command
Previous Topic: Problem while accessing java code from oracle
Next Topic: problem with loading the ids into table
Goto Forum:
  


Current Time: Mon Dec 05 19:00:00 CST 2016

Total time taken to generate the page: 0.11301 seconds