I have a question about SQL..

From: c2061_at_bcline.com <(c2061_at_bcline.com)>
Date: 7 Apr 2003 08:26:12 -0700
Message-ID: <45064e2c.0304070726.a3f25b8_at_posting.google.com>


select * from emp order by deptno;

7782|CLARK|MANAGER|7839|1981-06-09|2450||10
7839|KING|PRESIDENT||1981-11-17|5000||10
7934|MILLER|CLERK|7782|1982-01-23|1300||10

7369|SMITH|CLERK|7902|1980-12-17|800||20
7876|ADAMS|CLERK|7788|1987-05-23|1100||20
7902|FORD|ANALYST|7566|1981-12-03|3000||20
7788|SCOTT|ANALYST|7566|1987-04-19|3000||20 7566|JONES|MANAGER|7839|1981-04-02|2975||20
7499|ALLEN|SALESMAN|7698|1981-02-20|1600|300|30
7698|BLAKE|MANAGER|7839|1981-05-01|2850||30
7654|MARTIN|SALESMAN|7698|1981-09-28|1250|1400|30
7900|JAMES|CLERK|7698|1981-12-03|950||30
7844|TURNER|SALESMAN|7698|1981-09-08|1500|0|30
7521|WARD|SALESMAN|7698|1981-02-22|1250|500|30

after 'deptno' sort

  1. I want to this result each deptno -> last man and previous man
7839|KING|PRESIDENT||1981-11-17|5000||10
7934|MILLER|CLERK|7782|1982-01-23|1300||10
7788|SCOTT|ANALYST|7566|1987-04-19|3000||20
7566|JONES|MANAGER|7839|1981-04-02|2975||20
7844|TURNER|SALESMAN|7698|1981-09-08|1500|0|30
7521|WARD|SALESMAN|7698|1981-02-22|1250|500|30 

2) I want to this result
except last man and then all emp selection

7782|CLARK|MANAGER|7839|1981-06-09|2450||10
7839|KING|PRESIDENT||1981-11-17|5000||10
7369|SMITH|CLERK|7902|1980-12-17|800||20
7876|ADAMS|CLERK|7788|1987-05-23|1100||20
7902|FORD|ANALYST|7566|1981-12-03|3000||20
7788|SCOTT|ANALYST|7566|1987-04-19|3000||20
7499|ALLEN|SALESMAN|7698|1981-02-20|1600|300|30
7698|BLAKE|MANAGER|7839|1981-05-01|2850||30
7654|MARTIN|SALESMAN|7698|1981-09-28|1250|1400|30
7900|JAMES|CLERK|7698|1981-12-03|950||30
7844|TURNER|SALESMAN|7698|1981-09-08|1500|0|30
Received on Mon Apr 07 2003 - 17:26:12 CEST

Original text of this message