Home » Other » Training & Certification » Retrieving First element of a Group By Item
Retrieving First element of a Group By Item [message #286713] Sun, 09 December 2007 23:53 Go to next message
saifurshaon
Messages: 29
Registered: December 2007
Location: Dhaka
Junior Member
how to select first row from each group.

--- use hr/hr
select e.employee_id,e.first_name,d.department_id
from employees e,departments d
where e.department_id=d.department_id
order by d.department_id asc

returns

EMPLOYEE_ID FIRST_NAME DEPARTMENT_ID
----------- -------------------- -------------
200 Jennifer 10
201 Michael 20
202 Pat 20
114 Den 30
115 Alexander 30
116 Shelli 30
119 Karen 30
118 Guy 30
117 Sigal 30
203 Susan 40
120 Matthew 50
121 Adam 50
123 Shanta 50
132 TJ 50
131 James 50
130 Mozhe 50
129 Laura 50
128 Steven 50
127 James 50

. . . bla bla bla . . .

my expected result set is some thing like this one

EMPLOYEE_ID FIRST_NAME DEPARTMENT_ID
----------- -------------------- -------------
200 Jennifer 10
201 Michael 20
114 Den 30
203 Susan 40
120 Matthew 50

. . . bla bla bla . . .

how to do it ?
_________________
Thanks,
Re: Retrieving First element of a Group By Item [message #286715 is a reply to message #286713] Sun, 09 December 2007 23:56 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>how to select first row from each group.
A database is like a collection of colored balls in a basket.
If there are more than 1 red ball in the basket; which is the 1st red ball?
Re: Retrieving First element of a Group By Item [message #286718 is a reply to message #286715] Mon, 10 December 2007 00:19 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

search for analytical function:
ROW_NUMBER()
RANK()
DENSE_RANK()


regards,
Re: Retrieving First element of a Group By Item [message #286720 is a reply to message #286713] Mon, 10 December 2007 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition:
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Retrieving First element of a Group By Item [message #286737 is a reply to message #286720] Mon, 10 December 2007 01:03 Go to previous messageGo to next message
saifurshaon
Messages: 29
Registered: December 2007
Location: Dhaka
Junior Member
I need a query without using analytical function.
would anyone like to give that solution?
Re: Retrieving First element of a Group By Item [message #286738 is a reply to message #286713] Mon, 10 December 2007 01:05 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I need a query without using analytical function.
would anyone like to give that solution?
NO
Re: Retrieving First element of a Group By Item [message #286739 is a reply to message #286737] Mon, 10 December 2007 01:06 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
saifurshaon wrote on Mon, 10 December 2007 08:03

I need a query without using analytical function.
would anyone like to give that solution?

Why? Oracle 9i is perfectly capable of coping with analytic functions. Is there something you're not telling us?

MHE
Re: Retrieving First element of a Group By Item [message #286745 is a reply to message #286739] Mon, 10 December 2007 01:16 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

your teacher said so.you can try MIN().

regards,
Re: Retrieving First element of a Group By Item [message #286758 is a reply to message #286745] Mon, 10 December 2007 01:37 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
dhananjay wrote on Mon, 10 December 2007 08:16


your teacher said so.
Question Classmates?

dhananjay wrote on Mon, 10 December 2007 08:16

you can try MIN().
And what is the relation between MIN (i.e. the smallest value) and first?

MHE

[Updated on: Mon, 10 December 2007 01:43]

Report message to a moderator

Re: Retrieving First element of a Group By Item [message #286768 is a reply to message #286758] Mon, 10 December 2007 01:42 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

yes,looking at the OP's post what he wanted is to retrive the first(min) empno from each dept.


regards,
Re: Retrieving First element of a Group By Item [message #286769 is a reply to message #286745] Mon, 10 December 2007 01:45 Go to previous messageGo to next message
saifurshaon
Messages: 29
Registered: December 2007
Location: Dhaka
Junior Member
i am trying to solve it without using analytical function just for my curiousity. There is no other thing beyond this.
If it is solved without using analytical fn then it could be used in other rdbms. Isn't it?
Dhananjay, would u like to give the full query rather than hints?
Re: Retrieving First element of a Group By Item [message #286770 is a reply to message #286768] Mon, 10 December 2007 01:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Confused Ok. Still seems odd to me but as an academic exercise I'll tag along. What do we have so far, Saifur Rashid Shaon?
* You cannot use analytic functions because - according to your classmate dhananjay - your teacher told you so.
* You can use aggregates (MIN() + GROUP BY).

What's your next move?

MHE

[Updated on: Mon, 10 December 2007 01:47]

Report message to a moderator

Re: Retrieving First element of a Group By Item [message #286772 is a reply to message #286769] Mon, 10 December 2007 01:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
saifurshaon wrote on Mon, 10 December 2007 08:45

If it is solved without using analytical fn then it could be used in other rdbms. Isn't it?
Ouch, generic code. I've seen it causing troubles and performance issues over and over and over again.

saifurshaon wrote on Mon, 10 December 2007 08:45

Dhananjay, would u like to give the full query rather than hints?
And where's the fun in that?

- Select the smallest empno + the deptno for each deptno. Use the output of that query to join with the employee table again.

MHE
Re: Retrieving First element of a Group By Item [message #286780 is a reply to message #286772] Mon, 10 December 2007 01:58 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member


@Maaher.

Quote:

You cannot use analytic functions because - according to your classmate dhananjay


i am not his classmate.


regards,
Re: Retrieving First element of a Group By Item [message #286781 is a reply to message #286769] Mon, 10 December 2007 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
saifurshaon wrote on Mon, 10 December 2007 08:45

i am trying to solve it without using analytical function just for my curiousity. There is no other thing beyond this.
If it is solved without using analytical fn then it could be used in other rdbms. Isn't it?
Dhananjay, would u like to give the full query rather than hints?

Would you like to solve it for your curiosity or would like we solve it for you?

Regards
Michel
icon12.gif  Re: Retrieving First element of a Group By Item [message #286945 is a reply to message #286713] Mon, 10 December 2007 10:53 Go to previous messageGo to next message
saifurshaon
Messages: 29
Registered: December 2007
Location: Dhaka
Junior Member
Laughing ha ha ha. I am amazed that oracle lovers, here in the forum are unable to solve my problem. They are more interested to gossip and talk useless matter.
@Maheer: Dhananjay is not my classmet. So please stop talkin nonsense.
Ok guys I dont need the query. I have come to know about forum members' mentality about any help concerns.
Re: Retrieving First element of a Group By Item [message #286946 is a reply to message #286713] Mon, 10 December 2007 11:22 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> I am amazed that oracle lovers, here in the forum are unable to solve my problem.
Not UNABLE, but unwilling.
Yes, it is YOUR problem; not ours.
Nobody here owes you any solution/answer.
Re: Retrieving First element of a Group By Item [message #286947 is a reply to message #286945] Mon, 10 December 2007 11:23 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This forums has helped other fellow forums members in a lot of different ways. I am member of many other forums but i find this forum very good because they make do things by which you learn a lot and if you are stuck they will give you hints to try to solve your problem rather than spoon feeding like what I am going to do now. Because by spoon feeding it will solve your problem for now but you will still be dependent on others which I personally feel a huge bottleneck.

SQL> select deptno, empno, ename from emp;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        20       7369 SMITH
        30       7499 ALLEN
        30       7521 WARD
        20       7566 JONES
        30       7654 MARTIN
        30       7698 BLAKE
        10       7782 CLARK
        20       7788 SCOTT
        10       7839 KING
        30       7844 TURNER
        20       7876 ADAMS

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        30       7900 JAMES
        20       7902 FORD
        10       7934 MILLER

SQL> select empno, ename, deptno from emp where empno in (select min(empno) from emp group by deptno
);

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7499 ALLEN              30
      7369 SMITH              20
      7782 CLARK              10




I think this is what @dhananjay meant. I think it is time for you to read the oracle reference manual. It has loads of valuable information. Read it, try it , google it and still if you are stuck come back here by all means. Believe me, if you chose to ignore only you will be the loser and not us. Because it's not everybody are ready to share the knowledge but this forum is not like that. There are loads of people here who are extremely knowledgeable and not only that they are ready to share their knowledge and expertise which you can't get it any books.

O.k enough lecturing and do some real work.

Happy learning

Regards

Raj

Re: Retrieving First element of a Group By Item [message #286948 is a reply to message #286713] Mon, 10 December 2007 11:47 Go to previous messageGo to next message
saifurshaon
Messages: 29
Registered: December 2007
Location: Dhaka
Junior Member
dear raj
thanks for ur nice reply. I am really grateful to u. Before ur reply what happended u just see

Quote:

'Would you like to solve it for your curiosity or would like we solve it for you?'


Quote:

Ok. Still seems odd to me but as an academic exercise I'll tag along. What do we have so far, Saifur Rashid Shaon?
* You cannot use analytic functions because - according to your classmate dhananjay - your teacher told you so.
* You can use aggregates (MIN() + GROUP BY).

What's your next move?


Look the difference between ur post and these posts. R they encouraging??
Anyways thanks again for ur helpful advice.
Regards


Re: Retrieving First element of a Group By Item [message #286952 is a reply to message #286713] Mon, 10 December 2007 12:10 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
saifurshaon,
Please read & FOLLOW posting guidelines as stated in URL above;
Re: Retrieving First element of a Group By Item [message #286955 is a reply to message #286948] Mon, 10 December 2007 12:29 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Saifurshaon, although S. Rajaram provided a working example based on Scott's schema, I'd like you to try the following, significantly improved query - it will require much less time as it is optimized.
SEL eno, enam, dno
-> emp
? eno IN (SEL m(eno) 
          -> emp
          GBY dno
         );
Re: Retrieving First element of a Group By Item [message #286956 is a reply to message #286955] Mon, 10 December 2007 12:32 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oops! I forgot to mention (in my previous post) that, if you'd like to select the 'last' employee, just change 'm(eno)' with 'M(eno)' ('m' stands for 'min', while 'M' stands for 'MAX').

Also, I hope you have applied the Oracle IM speak patch for SQL & PL/SQL; I can't remember the patch number at the moment, but I'm quite sure you'll be able to download and install it using Google.
Re: Retrieving First element of a Group By Item [message #287079 is a reply to message #286945] Tue, 11 December 2007 01:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
saifurshaon wrote on Mon, 10 December 2007 17:53


@Maaher: Dhananjay is not my classmate. So please stop talkin nonsense.
I already got that Wink. It wasn't nonsensical, it made perfect good sense: he did know what your teacher wanted (at least that's what he wrote).
That said: I was not trying to pull your leg or anything. I thought I (and the others here as well) handed you everything you needed. I understood that you wanted to try this yourself. Enough of that, back on topic.

I said you could use MIN() and GROUP BY:
SELECT MIN(employee_id) min_empid
     , department_id
FROM   employees
GROUP BY department_id
/

That's step one: you have the smallest employee number for each department. Now you need to join this with the employee table again to fetch the details you want. In the thread you'll find an example using a subquery and the IN operator. You could also use a join:
SQL> SELECT e.employee_id
  2       , e.first_name
  3  FROM   employees e
  4     ,   ( -- smallest employee_id
  5           SELECT MIN(employee_id) min_empid
  6                , department_id
  7           FROM   employees
  8           GROUP BY department_id
  9         ) s
 10  WHERE  s.min_empid   = e.employee_id
 11  /

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        108 Nancy
        114 Den
        178 Kimberely
        201 Michael
        204 Hermann
        100 Steven
        205 Shelley
        120 Matthew
        203 Susan
        145 John
        200 Jennifer

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        103 Alexander

12 rows selected.

MHE
Re: Retrieving First element of a Group By Item [message #287252 is a reply to message #286713] Tue, 11 December 2007 12:20 Go to previous messageGo to next message
saifurshaon
Messages: 29
Registered: December 2007
Location: Dhaka
Junior Member
Thanks to Littlefoot and Maheer.
Re: Retrieving First element of a Group By Item [message #287264 is a reply to message #287252] Tue, 11 December 2007 14:22 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
To me? For what? If you truly understood what I meant to say, great! If not, well, that it is thanks for nothing.

Never mind; I'm glad you got the solution for your problem.
Re: Retrieving First element of a Group By Item [message #287265 is a reply to message #287252] Tue, 11 December 2007 14:27 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
saifurshaon wrote on Tue, 11 December 2007 18:20

Thanks to Littlefoot and Maheer.

Who is Maheer? There was plenty more I said after this, but I deleted it before I posted. @saifurshaon Please have the common courtesy to spell people's 'names' correctly. Not to do so, is simply rude.
Previous Topic: Removing duplicates in a string.
Next Topic: hi
Goto Forum:
  


Current Time: Sun Dec 04 10:59:18 CST 2016

Total time taken to generate the page: 0.20185 seconds