Re: help with SQL Select Statement

The following are a couple more solutions to add to the collection:

SQL> desc emp

 Name                               Null?    Type
 ---------------------------------- -------- ----------
 SERIAL_NO                                   NUMBER
 STARTD                                      DATE
 STOPD                                       DATE

SQL> select * from emp;

SERIAL_NO STARTD STOPD

--------- --------- ---------
        1 11-SEP-85
        2 12-JAN-86
        3 18-FEB-86
        4 18-FEB-86 20-MAR-86
        5 25-MAY-86
        6 11-NOV-86
        7 11-NOV-86 12-NOV-86
        8 22-MAR-87
        9 12-SEP-89

9 rows selected.

SQL> select serial_no, startd, stopd
  2 from emp
  3 minus
  4 select serial_no, startd, stopd
  5 from emp b
  6 where stopd is null
  7 and exists (select startd from emp where stopd is not null and startd = b.startd);

SERIAL_NO STARTD STOPD

--------- --------- ---------
        1 11-SEP-85
        2 12-JAN-86
        4 18-FEB-86 20-MAR-86
        5 25-MAY-86
        7 11-NOV-86 12-NOV-86
        8 22-MAR-87
        9 12-SEP-89

7 rows selected.

SQL> select a.serial_no, a.startd, a.stopd   2 from emp a,
  3 (select startd, max(stopd) m from emp group by startd) b   4 where a.startd = b.startd
  5 and ((a.stopd = b.m) or (a.stopd is null and b.m is null))   6 order by 2;

SERIAL_NO STARTD STOPD

--------- --------- ---------
        1 11-SEP-85
        2 12-JAN-86
        4 18-FEB-86 20-MAR-86
        5 25-MAY-86
        7 11-NOV-86 12-NOV-86
        8 22-MAR-87
        9 12-SEP-89

7 rows selected.

smagadi_at_yahoo.com wrote:

> Hi,
>
> I have a emp table which has data as follows:
>
> Serial No. Start Stop
> 1 11-SEP-85
> 2 12-JAN-86
> 3 18-FEB-86
> 4 18-FEB-86 20-Mar-86
> 5 25-MAY-86
> 6 11-NOV-86
> 7 11-NOV-86 12-NOV-86
> 8 22-MAR-87
> 9 12-SEP-89
>
> I want to write a SQL query which retrives:
>
> Serial No. Start Stop
> 1 11-SEP-85
> 2 12-JAN-86
> 4 18-FEB-86 20-Mar-86
> 5 25-MAY-86
> 7 11-NOV-86 12-NOV-86
> 8 22-MAR-87
> 9 12-SEP-89
>
> As shown in the example above if there are two start dates which are
> same, it should retrive the row with the stop date.
>
> Any help would be appreciated.
>
> Thank you
>
> sashi
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Sep 14 1999 - 23:42:53 CDT