Postgres Query Improvement needed

Question

I have to improvement this query:

SELECT pt.equip, pt.date, pt.cod,  
(  
SELECT pt2.date  
FROM "TABLE" pt2  
WHERE 1=1  
AND pt2.equip = pt.equip  
AND CAST(pt2.date AS DATE) between '2015/12/01' AND '2015/12/02'  
AND pt2.cod in (17, 84, 85)  
AND pt2.date > pt.date  
ORDER BY pt2.date  
LIMIT 1  
)  
FROM "TABLE" pt   
WHERE pt.cod is not null  
AND pt.equip IN (2,3,8,7)  
AND CAST(pt.date AS DATE) between '2015/12/01' AND '2015/12/02'  
AND pt.cod in (16, 81, 82)

The subquery is the same table of the main query.
The cod column defines the initial period in the first query, and cod defines the end of the second query.
How to make a query with the columns equip, dateInitial (first query), dateEnd (second query)???

Can you share some sample data and the result you're trying to get? — Mureinik
– Mureinik, Commented Dec 11, 2015 at 17:34
Please edit this into the question - it's impossible to understand it in the comments, without any formatting. — Mureinik
– Mureinik, Commented Dec 11, 2015 at 17:41

Gordon Linoff · Accepted Answer · 2015-12-11 17:38:44Z

2

One approach to this query is to do a LEFT JOIN between the tables and then use DISTINCT ON:

SELECT DISTINCT ON (pt.equip, pt.date, pt.cod) pt.*, pt2.*
FROM "TABLE" pt LEFT JOIN
     "TABLE" pt2
     ON pt2.equip = pt.equip AND
        CAST(pt2.date AS DATE) between '2015/12/01' AND '2015/12/02' AND
        pt2.cod in (17, 84, 85) AND
        pt2.date > pt.date
WHERE pt.cod is not null AND
      pt.equip IN (2,3,8,7) AND
      CAST(pt.date AS DATE) between '2015/12/01' AND '2015/12/02' AND
      pt.cod in (16, 81, 82)  
ORDER BY pt.equip, pt.date, pt.cod, pt2.date DESC;

Since version 9.3, Postgres has also supported lateral joins, which allow the subquery to be in the FROM clause, thereby making it easier to choose multiple columns from its result set.

answered Dec 11, 2015 at 17:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Query Improvement needed

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related