Re: SQL question

From: Andrew Pahomow <Pahomov_at_validio.com.ua>
Date: Thu, 2 Nov 2000 22:53:14 +0200
Message-ID: <3a027d34$1_at_miik2-ilt.ilt.kharkov.ua>

[Quoted] 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,
[Quoted] (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.
Received on Thu Nov 02 2000 - 21:53:14 CET