OraFAQ Forum: SQL & PL/SQL » using max and count

Home » SQL & PL/SQL » SQL & PL/SQL » using max and count

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

using max and count [message #1655]

Mon, 20 May 2002 04:26

Michael
Messages: 61
Registered: October 1999

Member

hi
i have to display the store with the maximun amount of employees, but i in order to know the number of employees of each store, i must count themn first
so i think i need count and max in the same query or subquery
thanks

Report message to a moderator

Re: using max and count [message #1656 is a reply to message #1655]

Mon, 20 May 2002 05:46

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Here's an example using emp. It selects the department number which contains the largest number of employees:

select * from ( select count(*)
, deptno
from emp
group by deptno
order by 1 desc
)
where rownum < 2

As you can see, there's no need for the MAX function.

Perhaps someone else knows a better way?

MHE

Report message to a moderator

doubt regarding the reply using max and count [message #1733 is a reply to message #1655]

Fri, 24 May 2002 09:08

pavani
Messages: 32
Registered: April 2002

Member

i think we must not use orderby clause in subquries.
i know that we can use orderby clause in inline views.
plz let me know about this
thankq

Report message to a moderator

Previous Topic:	Previous Day?
Next Topic:	Creating Users and allowing them access to your tables or objects

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 15:02:12 CDT 2024