How to select

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 23 May 2006 22:16 GMT

I have a history table, and I want to select the first date occurrence
of a type 'A' event after the last event of type other than 'A', if
there is one. The best I can come up with is below. This seems
convoluted, especially compared with a cursor over the history in
descending order. Is this the best I can do in sql? Would a UDF using a
cursor, returning the chosen date be faster or more effective? How do I
extend this to work in the case the are no type<>'A' events? To make the
task more challenging, the type='A' phrase is significantly more complex
and this is just one column in a select over a 13-way join.

select startdate as first_contiguous
from history
where type='A'
and startdate=
    (select min(startdate)
    from history
    where type='A'
    and startdate>=
        (select max(startdate)
        from history
        where type<>'A'))

Reply to this Message

ChrisC - 24 May 2006 00:50 GMT

How about:

with last_non_a(startdate) as (select max(startdate) from history where
type != 'A'),
recent_events(start_date, rownum) as (select startdate, row_number()
over (order by startdate asc) from history h, last_non_a l where h.type
= 'A' and h.startdate > l.startdate)
select startdate form recent_events where rownum = 1

I guess that isn't really any less convoluted, but it might possibly be
quicker.

-Chris

Reply to this Message

Bob Stearns - 24 May 2006 06:45 GMT

> How about:
>
[quoted text clipped - 9 lines]
>
> -Chris

Thanks, I'll try it.

Reply to this Message

Brian Tkatch - 24 May 2006 15:10 GMT

> I have a history table, and I want to select the first date occurrence
> of a type 'A' event after the last event of type other than 'A', if
[quoted text clipped - 17 lines]
> from history
> where type<>'A'))

SELECT
MIN(StartDate)
FROM
History
WHERE
Type = 'A'
AND (
NOT EXISTS(SELECT * FROM History WHERE Type <> 'A')
OR (
EXISTS(SELECT * FROM History WHERE Type <> 'A')
AND StartDate > (SELECT MAX(StartDate) FROM History
WHERE Type <> 'A')
)
)

A less confusing query could be something like:

SELECT
MIN(StartDate)
FROM
History
WHERE
Type = 'A'
AND StartDate >
(SELECT COSLESCE(MAX(StartDate), DATE('1/1/1900')) FROM History
WHERE Type <> 'A')

B.

Reply to this Message

Bob Stearns - 24 May 2006 19:38 GMT

>>I have a history table, and I want to select the first date occurrence
>>of a type 'A' event after the last event of type other than 'A', if
[quoted text clipped - 46 lines]
>
> B.

Very good thought. Put a fence value in in the missing case. I've used
the technique many times, just never in sql. Thank you.

Reply to this Message