Re: SQL Problem

You need to look at analytic functions,
particularly the LAG() or LEAD()
functions. Your query will have
two extra (derived) columns, which
are lagged copies of the transaction type, and then you can do a simple comparison
for

    tttype = 4
   and lag(t_type1,1) over(...) = 92
   and lag(t_type1,2) over(...) = 94

The main query will have to be in an
inline view, as you can't put analytic functions in a where clause.

Tom Kyte has various examples, my
site has a couple.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004

"MAB" <bad-email_at_nowhere.com> wrote in message news:34bv4uF474jt4U1_at_individual.net...
> I'm using 9i DB. I have table Ledger with pri. key Ledger_id, It has say
> two
> other fields Customer_ID and Transaction_Type_id. Now ofcourse the other
> two
> are not unique. What I want is to find all records with
> transaction_type_id
> = 4 with the condition that the previous transaction_type_id is 92 and one
> before the previous is 24 for the same customer_id ( the customer_id
> should
> be the same)
>
> So for example
>
> Ledger_id Customer_id Transaction_type_id
>
> 1 1 1
> 2 1 24
> 3 2 5
> 4 1 92
> 5 3 16
> 6 1 4
>
>
> You can see that ledger_id # 6 is the record my query should bring because
> it has ( for customer_id 1)
>
> 1. Transaction_type_id = 4
> 2. previous TTI = 92
> 3 2nd Last TTI = 24
>
> so it meets all conditions.
> What would be the sql query that would bring all such records which meet
> the
> above conditions
>
> thx.
>
>
Received on Sun Jan 09 2005 - 02:23:18 CST