Re: Complex query

Leonardo M. Ramé-2 wrote
> Hi, I'm looking for help with this query.
>
> Table Tasks:
>
> IdTask  StatusCode  StatusName
> ----------------------------------
> 1       R           Registered
> 1       S           Started
> 1       D           Dictated
> 1       F           Finished
> 1       T           Transcribed
> ----------------------------------
> 2       R           Registered
> 2       S           Started
> 2       T           Transcribed
> 2       F           Finished
>
> As you can see, I have a table containing tasks and statuses. What I
> would like to get is the list of tasks, including all of its steps, for
> only those tasks where the StatusCode sequence was S followed by T.
>
> In this example, the query should only return task Nº 2:
>
> 2       R           Registered
> 2       S           Started
> 2       T           Transcribed
> 2       F           Finished
>
> Can anybody help me with this?.

First you need to decide how tell the database that R-S-T-F is ordered and
then maybe you can use window functions, specifically "lag(col, -1) over
(...)", to determine what the prior row's code is and act accordingly.

Put that into a sub-query and return the "IdTask" to the outer query's where
clause.

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/Complex-query-tp5798061p5798068.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.