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.