Re: help with SQL Select Statement

Looks ugly, but this is because the task itself is against SQL's nature:

select no#, a.start#, decode( cnt#, 2, stop#, null ) from (select no#, start#, stop# from tt) a, (select START#, count(*) cnt# from tt group by start#) b where a.start# = b.start# and not (stop# is null and cnt#=2);

Maybe there are better ways, but since you asked for help...

Regards,
Karen Abgarian.

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 - 15:04:37 CDT