Thread: Complex query
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?. Regards, -- 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
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.
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Leonardo M. Ramé > Sent: Monday, March 31, 2014 2:38 PM > To: PostgreSql-general > Subject: [GENERAL] Complex query > > 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?. > > Regards, > -- > 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Leonardo, Unless you add one more column to your Tasks table, specifically: StatusTimestamp as in: IdTask StatusCode StatusName StatusTimestamp You cannot find which record in the table follows which, because order in which records returned from the database is notguaranteed until you add "ORDER BY" clause to your SELECT statement. Regards, Igor Neyman
On 31/03/2014 19:38, Leonardo M. Ramé 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. How do you know the sequence in which the statuses occurred? Is there another column with a timestamp or something? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 2014-03-31 18:48:58 +0000, Igor Neyman wrote: > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > > owner@postgresql.org] On Behalf Of Leonardo M. Ramé > > Sent: Monday, March 31, 2014 2:38 PM > > To: PostgreSql-general > > Subject: [GENERAL] Complex query > > > > Hi, I'm looking for help with this query. > > > > Leonardo, > > Unless you add one more column to your Tasks table, specifically: StatusTimestamp as in: > > IdTask StatusCode StatusName StatusTimestamp > > You cannot find which record in the table follows which, because order in which records returned from the database is notguaranteed until you add "ORDER BY" clause to your SELECT statement. > > Regards, > Igor Neyman > > You are right, let's add the Id column. This is just an example, the real table (a view) contains both, the Id and a timestamp: Id IdTask StatusCode StatusName ---------------------------------- 1 1 R Registered 2 1 S Started 3 1 D Dictated 4 1 F Finished 5 1 T Transcribed ---------------------------------- 6 2 R Registered 7 2 S Started 8 2 T Transcribed 9 2 F Finished After adding the Id column, can I use a window function to get what I need?. Regards, -- 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
> -----Original Message----- > From: Leonardo M. Ramé [mailto:l.rame@griensu.com] > Sent: Monday, March 31, 2014 2:56 PM > To: Igor Neyman > Cc: PostgreSql-general > Subject: Re: [GENERAL] Complex query > > On 2014-03-31 18:48:58 +0000, Igor Neyman wrote: > > > -----Original Message----- > > > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > > > owner@postgresql.org] On Behalf Of Leonardo M. Ramé > > > Sent: Monday, March 31, 2014 2:38 PM > > > To: PostgreSql-general > > > Subject: [GENERAL] Complex query > > > > > > Hi, I'm looking for help with this query. > > > > > > > Leonardo, > > > > Unless you add one more column to your Tasks table, specifically: > StatusTimestamp as in: > > > > IdTask StatusCode StatusName StatusTimestamp > > > > You cannot find which record in the table follows which, because order in > which records returned from the database is not guaranteed until you add > "ORDER BY" clause to your SELECT statement. > > > > Regards, > > Igor Neyman > > > > > > You are right, let's add the Id column. This is just an example, the real table (a > view) contains both, the Id and a timestamp: > > Id IdTask StatusCode StatusName > ---------------------------------- > 1 1 R Registered > 2 1 S Started > 3 1 D Dictated > 4 1 F Finished > 5 1 T Transcribed > ---------------------------------- > 6 2 R Registered > 7 2 S Started > 8 2 T Transcribed > 9 2 F Finished > > After adding the Id column, can I use a window function to get what I need?. > > Regards, > -- > 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 D.Johnston showed how to use windows function in this case. Regards, Igor Neyman
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
Leonardo M. Ramé-2 wrote > 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 First you want to include an ORDER BY in the OVER(...) clause, and probably a PARTITION BY as well. Then you move that to a sub-query (for example): SELECT * FROM tbl WHERE tbl.idtask IN ( SELECT lag.idtask FROM ( lag_query_here ) lag WHERE lag.code = 'T' and lag.lg = 'S' ); David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Complex-query-tp5798061p5798087.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 2014-03-31 12:16:53 -0700, David Johnston wrote: > Leonardo M. Ramé-2 wrote > > 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 > > First you want to include an ORDER BY in the OVER(...) clause, and probably > a PARTITION BY as well. > > Then you move that to a sub-query (for example): > > SELECT * > FROM tbl > WHERE tbl.idtask IN ( > SELECT lag.idtask FROM ( lag_query_here ) lag WHERE lag.code = 'T' and > lag.lg = 'S' > ); > > David J. > Great!, that's what I needed, thank you. -- 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