Complex query

Started by Leonardo M. Raméabout 12 years ago9 messagesgeneral

l.rame@griensu.com

about 12 years ago

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

David G. Johnston

david.g.johnston@gmail.com

about 12 years ago

In reply to: Leonardo M. Ramé (#1)

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Igor Neyman

ineyman@perceptron.com

about 12 years ago

In reply to: Leonardo M. Ramé (#1)

Re: Complex query

-----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 not guaranteed until you add "ORDER BY" clause to your SELECT statement.

Regards,
Igor Neyman

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Raymond O'Donnell

rod@iol.ie

about 12 years ago

In reply to: Leonardo M. Ramé (#1)

Re: Complex query

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Leonardo M. Ramé

l.rame@griensu.com

about 12 years ago

In reply to: Igor Neyman (#3)

Re: 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?.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Igor Neyman

ineyman@perceptron.com

about 12 years ago

In reply to: Leonardo M. Ramé (#5)

Re: Complex query

-----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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Leonardo M. Ramé

l.rame@griensu.com

about 12 years ago

In reply to: David G. Johnston (#2)

Re: Complex query

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David G. Johnston

david.g.johnston@gmail.com

about 12 years ago

In reply to: Leonardo M. Ramé (#7)

Re: Complex query

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Leonardo M. Ramé

l.rame@griensu.com

about 12 years ago

In reply to: David G. Johnston (#8)

Re: Complex query

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general