3

I have two tables:

CREATE TABLE one (
  id int4 primary key,
  p_id int4,
  k_id int4,
  c_id int4
);

CREATE TABLE two(
 id int4 primary key,
 p_id int4,
 k_id int4,
 t_id int4,
 pos int4
);

Table one: is a join table, I'm using it in this report only because of the c_id and to limit the number of row.

Table two: contains my data, a p_id, k_id and t_id can have many pos, so I'm grouping them and using min(), to get the min pos. I've tried some ways to get all my data for a k_id and p_id here is a way (with this solution I was not able to order by pos), so I end up doing this (see fiddle):

SELECT
  o.p_id,
  o.k_id,
  min(t.pos) as t_pos,
  min(t1.pos) as t1_pos
FROM one o
  LEFT JOIN two t ON t.p_id = o.p_id 
    AND t.k_id = o.k_id
    AND t.t_id = 1
  LEFT JOIN two t1 ON t1.p_id = o.p_id 
    AND t1.k_id = o.k_id
    AND t1.t_id = 2
WHERE o.p_id = 1 AND o.c_id = 1
GROUP BY 1, 2
LIMIT 1

This solution is 2-3 times slower then @Erwin Brandstetter's solution from the other post, but sorting t_pos, t1_pos is possible and also getting all data where type_id = 1 and pos = 1 for example.

Is there a better way of achieving the same output and improve the speed and be able to sort my data (t_pos and t1_pos columns)?

1

Try how good this works for you

Converted left join to one inner join for table Two for both t_id's. And on computing min, using case statement to split to two columns based on the value t_id. What we save here is scanning the table two time and an inner join compared to left join

SELECT
    o.p_id,
    o.k_id,
    MIN(case when t.t_id = 1 then t.pos else null end) AS t_pos,
    MIN(case when t.t_id = 2 then t.pos else null end) AS t1_pos
FROM
    one o
INNER JOIN two T ON t.p_id = o.p_id
AND t.k_id = o.k_id
AND t.t_id =any ( 1,2)
WHERE
    o.p_id = 1
AND o.c_id = 1
GROUP BY
    1,
    2
LIMIT 1
  • A few explaining words are always a good idea, just saying. – tombom Jul 17 '15 at 10:54
  • Yeah, that's true. And here it comes. Converted left join to one inner join for table Two for both t_id's. And on computing min, using case statement to split to two columns based on the value t_id. What we save here is scanning the table two time and an inner join compared to left join – Aneesh Mon N Jul 17 '15 at 11:00
  • 3
    It would be much better to edit your answer than add explanation in a comment. Comments are temporary and not searchable. – Paul White 9 Jul 17 '15 at 11:16
  • @Aneesh Mon N Thanks, this is faster then mine, do you know how can I get for example, all rows where pos = 1 and type = 1? – RockNinja Jul 17 '15 at 11:25
  • Are you looking for adding a having clause to set pos=1 and type =1..? if so, its like below. After group by Add "HAVING MIN(case when t.t_id = 1 then t.pos else null end) =1 AND MIN(case when t.t_id = 2 then t.pos else null end) =1 – Aneesh Mon N Jul 17 '15 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.