Re: SQL question

You are an SQL King!

This works on 8.1.6 (although not on 8.0.5 which we use - must upgrade!). Im going to try the function which was also posted now.

Thanks.

In article <3a027d34$1_at_miik2-ilt.ilt.kharkov.ua>,   "Andrew Pahomow" <Pahomov_at_validio.com.ua> wrote:
> select d.dept, name, salary from
> (select dept, min(rn) min_row from
> (select a.*,rownum rn from
> (select s.* from employees s order by dept,salary desc) a) b group by
 dept)
> c,
> (select a.*,rownum rown from (select s.* from employees s order by
> dept,salary desc) a) d
> where d.rown-min_row < 3 and d.dept = c.dept
>
> tested on you test data :)
>
> <xdba_at_my-deja.com> wrote in message news:8ts7cq$ch2
 $1_at_nnrp1.deja.com...
> > Thanks to everyone for helping.
> >
> > I tried this but it doesn't like the 'order by' in the embedded
 select.
> >
> > I've tried this:-
> > select * from (select dept, name, salary from employees)
> > where rownum < 4 and dept='1'
> > order by salary desc;
> >
> > but it just gives me the first three records, not the top three for
> > each department.
> >
> > I'm still working on. If it helps, here's the test data:-
> >
> > CREATE TABLE employees
> > (Dept NUMBER,
> > Name VARCHAR2(20),
> > Salary NUMBER);
> > INSERT INTO employees
> > VALUES (1, 'Smith', 1000);
> > INSERT INTO employees
> > VALUES (1, 'Jones', 2000);
> > INSERT INTO employees
> > VALUES (1, 'White', 1000);
> > INSERT INTO employees
> > VALUES (1, 'Black', 3000);
> > INSERT INTO employees
> > VALUES (1, 'Red', 1000);
> > INSERT INTO employees
> > VALUES (1, 'Blue', 1000);
> > INSERT INTO employees
> > VALUES (1, 'Smith', 5000);
> > INSERT INTO employees
> > VALUES (2, 'Apple', 1000);
> > INSERT INTO employees
> > VALUES (2, 'Pear', 3000);
> > INSERT INTO employees
> > VALUES (2, 'Peach', 1000);
> > INSERT INTO employees
> > VALUES (2, 'Lemon', 4000);
> > INSERT INTO employees
> > VALUES (2, 'Melon', 2000);
> > INSERT INTO employees
> > VALUES (2, 'Peach', 3000);
> > INSERT INTO employees
> > VALUES (2, 'Prune', 1000);
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 03 2000 - 17:40:21 CET