CODEWITHSUNDEEP
sqlpostgresqloptimization
vbargl
vbargl

Reputation: 651

Could this query be optimized?

My goal is to select record by two criterias that depend on each other and group it by other criteria.

I found solution that select record by single criteria and group it

SELECT *
FROM "records"
NATURAL JOIN (
  SELECT "group", min("priority1") AS "priority1" 
  FROM "records" 
  GROUP BY "group") AS "grouped"

I think I understand concept of this searching - select properties you care about and match them in original table - but when I use this concept with two priorities I get this monster

SELECT *
FROM "records"
  NATURAL JOIN (
    SELECT *
    FROM (
      SELECT "group", "priority1", min("priority2") AS "priority2"
      FROM "records"
      GROUP BY "group", "priority1") AS "grouped2"
    NATURAL JOIN (
      SELECT "group", min("priority1") AS "priority1"
      FROM "records"
      NATURAL JOIN (
        SELECT "group", "priority1", min("priority2") AS "priority2"
        FROM "records"
        GROUP BY "group", "priority1") AS "grouped2'"
      GROUP BY "group") AS "GroupNested") AS "grouped1"

All I am asking is couldn't it be written better (optimalized and looking-better)?

JSFIDDLE

---- Update ----

The goal is that I want select single id for each group by priority1 and priority2 should be selected as first and then priority2).

Example: When I have table records with id, group, priority1 and priority2 with data:

id , group , priority1 , priority2
56 ,     1 ,         1 ,         2  
34 ,     1 ,         1 ,         3  
78 ,     1 ,         3 ,         1

the result should be 56,1,1,2. For each group search first for min of priority1 than search for min of priority2.

I tried combine max and min together (in one query`, but it does not find anything (I do not have this query anymore).

Upvotes: 0

Views: 90

Answers (3)

joop
joop

Reputation: 4523

EXISTS() to the rescue! (I did some renaming to avoid reserved words) 

SELECT *
FROM zrecords r
WHERE NOT EXISTS (
    SELECT *
    FROM zrecords nx
    WHERE nx.zgroup = r.zgroup
    AND ( nx.priority1 < r.priority1
        OR nx.priority1 = r.priority1 AND nx.priority2 < r.priority2
        )
    );

Or, to avoid the AND / OR logic, compare the two-tuples directly:

SELECT *
FROM zrecords r
WHERE NOT EXISTS (
    SELECT *
    FROM zrecords nx
    WHERE nx.zgroup = r.zgroup
    AND (nx.priority1, nx.priority2) < (r.priority1 , r.priority2)
    );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Simply use row_number() . . . once:

select r.*
from (select r.*,
             row_number() over (partition by "group" order by priority1, priority2) as seqnum
      from records r
     ) r
where seqnum = 1;

Note: I would advise you to avoid natural join. You can use using instead (if you don't want to explicitly include equality comparisons).

Queries with natural join are very hard to debug, because the join keys are not listed. Worse, "natural" joins do not use properly declared foreign key relationships. They depend simply on columns that have the same name.

In tables that I design, they would never be useful anyway, because almost all tables have createdAt and createdBy columns.

Upvotes: 0

areklipno
areklipno

Reputation: 528

maybe this is what you expect

 with dat as (
SELECT "group" grp
, priority1, priority2, id
, row_number() over (partition by "group" order by priority1) +
      row_number() over (partition by "group" order by priority2) as lp
FROM "records")

select dt.grp, priority1, priority2, dt.id
from dat dt
join (select min(lp) lpmin, grp from dat group by grp) dt1 on (dt1.lpmin = dt.lp and dt1.grp =dt.grp)

Upvotes: 0

Related Questions