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