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

Graham Miller <lgmiller_at_elmrd.u-net.com> wrote:
: 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)

-- 

Hi,

If more than one department has the same employee count they will all be 
returned by this query (they both have the highest so wich one to choose ?).
If all departments have the same employee counts then all departments are 
returned.  

If the requirements for the query are that only a limited numbers of rows 
should be returned then you could use rownum:

2 > (select...)
and rownum <= 2
order by emp_c.emp_count desc
/
 
Since more than one department have the same emp_count and I just want one of
them it does not matter wich one I choose, thus it does not matter that rownum
is assigned before the order by.  Rownum is, by the way, always the last 
statement in a where clause to be applied. 

About the efficiency of the query I do agree with Graham. It is probably not 
very efficient, even in 7.3,  if the number of employees grows large. 
So you might want to create an aggregate column on you dept table, 
ie employee_count which you maintain everytime the employee count of the 
department changes. 
 
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.