On 2014-03-31 11:46:28 -0700, David Johnston wrote: > 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. > > Thanks David, I hope I understood what you mean. After adding the Id column, I came up with this query: ris=# select lag.id, lag.idtask, lag.code, lag.lg from (select idtask, code, id, lag(code, -1) over () as lg from tasks_test) as lag; id | idtask | code | lg ----+--------+------+---- 1 | 1 | R | S 2 | 1 | S | D 3 | 1 | D | F 4 | 1 | F | T 5 | 1 | T | R 6 | 2 | R | S 7 | 2 | S | T 8 | 2 | T | F 9 | 2 | F | (9 rows) Row nº 7 meets the condition, but I don't want to show only that row, I would like to show this: 6 | 2 | R | S 7 | 2 | S | T 8 | 2 | T | F 9 | 2 | F | Any hint?. -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
