Re: sql : the 2 best, how ???

Hello,

I haven't tried this query (i don't have version 7.3) but what is returned if all departments have the same number of employees (worse case)?
I suspect nothing. It looks similar to an Oracle example, but that relies on the returned counts being unique or almost so. That doesn't mean it is wrong, just limited in application. Also it is very expensive, i think, on earlier versions of Oracle.

I may have missed the point entirely, if so, i am sorry.

Someone else has pointed out that if a join is used then row numbers are assigned after the 'order by' clause. I haven't tried it. I didn't know about it (not unusual). It sounds almost too good to be true, but very nice!  

graham (aka grumpy)

| Luc Hendrickx <paratel_at_mail.interpac.be> asked this question:
| : Hi,
 

| : I have a serious problem and following will illustrate that by the emp -
| : table.
| : I would like to get 2 department with hight personel count (ordered by
| : count).
 

| : select deptno, count(*)
| : from emp
| : group by deptno;
 

| : this gives me :
 

| : > DEPTNO COUNT(*)
| : > ---------- ----------
| : > 10 4
| : > 20 8
| : > 30 7
| : >
| : and I would like to have this :
| : 20 8
| : 30 7
|

 [snip]
|
| : Luc Hendrickx
| : Paratel Interactive
| : Vilvoorde (Belgium)
|
|
| --
|

Heggelund <d92hegge_at_ix_prod.hfk.mil.no> replied...

| Hi,
|
| I think this statement should work:
|
| select emp_c.deptno,emp_c.emp_count
| from (select deptno,count(*) emp_count
| from emp
| group by deptno) emp_c
| where 2 > (select count(*)
| from (select count(*) emp_count
| from emp
| group by deptno) emp_c2
| where emp_c2.emp_count > emp_c.emp_count)
| order by emp_c.emp_count desc
| /
|
| By changing the number that are checked against the subquery you could adjust
| the number of rows returnied. If you want to return the 1000 departments with
| the highest empoyee count, just change the number from 2 to 1000.
|
| Rgds
| Steinar Heggelund
|
|
| ----------------------------------------------------------------------------
| I'm employed in the Norwegian consulting company Opus One AS.
| I have 7 years experience with Oracle products, mainly the database.
| We are a small company which offers consulting services in design,
| implementation and tuning of databases and applications based on Oracle.
|
| My postings represent my opinions and they may be wrong.

Graham Miller ...
Opinions expressed are mine, they are free, and worth exactly what they cost. Received on Fri Jun 06 1997 - 00:00:00 CDT