Grouping values in order to sort by minimum of a value, then other fields then by that value itself

Question

I would like to sort my data by an aggregate value, then by other fields, then by the unaggregated value.

I have the following schema:

CREATE TABLE priority_t (
  id          NUMERIC(10),
  priority    NUMERIC(10),
  CONSTRAINT priority_t_pk PRIMARY KEY (id)
);

CREATE TABLE value_t (
  id          NUMERIC(10),
  value       NUMERIC(10),
  CONSTRAINT value_t_pk PRIMARY KEY (id)
);

CREATE TABLE file_t (
  id          NUMERIC(10),
  CONSTRAINT file_t_pk PRIMARY KEY (id)
);

CREATE TABLE main_t (
  id          NUMERIC(10),
  priority_id NUMERIC(10),
  value_id    NUMERIC(10),
  file_id     NUMERIC(10),
  CONSTRAINT main_t_pk PRIMARY KEY (id),
  CONSTRAINT priority_t_fk FOREIGN KEY (priority_id) REFERENCES priority_t(id),
  CONSTRAINT value_t_fk FOREIGN KEY (value_id) REFERENCES value_t(id),
  CONSTRAINT file_t_fk FOREIGN KEY (file_id) REFERENCES file_t(id)
);

Then I insert the following data:

INSERT INTO priority_t (id, priority) VALUES (1, 10);
INSERT INTO priority_t (id, priority) VALUES (2, 20);

INSERT INTO value_t (id, value)       VALUES (1, 987);
INSERT INTO value_t (id, value)       VALUES (2, 876);
INSERT INTO value_t (id, value)       VALUES (3, 765);
INSERT INTO value_t (id, value)       VALUES (4, 654);

INSERT INTO file_t (id)               VALUES (111);
INSERT INTO file_t (id)               VALUES (222);
INSERT INTO file_t (id)               VALUES (333);
INSERT INTO file_t (id)               VALUES (444);

INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (1, 1, 1, 111);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (2, 2, 1, 111);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (3, 2, 2, 222);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (4, 1, 2, 333);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (5, 2, 3, 111);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (6, 1, 4, 444);
INSERT INTO main_t  (id, priority_id, value_id, file_id) VALUES (7, 2, 4, 444);

COMMIT;

And I want to get the following result:

 min_priority | priority | value | value_id | file_id
   (hidden)   |          |       | (hidden) |        
--------------+----------+-------+----------+---------
           10 |       10 |   654 |        4 |     444 
           10 |       20 |   654 |        4 |     444
           10 |       10 |   876 |        2 |     333
           10 |       10 |   987 |        1 |     111
           10 |       20 |   987 |        1 |     111
           20 |       20 |   765 |        3 |     111
           20 |       20 |   876 |        2 |     222

I know how to sort them:

ORDER BY min_value ASC, value ASC, value_id ASC, priority ASC

But my problem is that I don't know how to group the values themselves: I keep getting duplicates in my rows, and/or incorrect values.

My closest attempt is the following:

WITH listing AS (
  SELECT m.id             AS main_id,
         p.id             AS priority_id,
         p.priority       AS priority,
         v.id             AS value_id,
         v.value          AS value,
         f.id             AS file_id
    FROM main_t m
           INNER JOIN priority_t p ON m.priority_id = p.id
           INNER JOIN value_t v    ON m.value_id = v.id
           INNER JOIN file_t f     ON m.file_id = f.id
)
SELECT min_p.min_priority AS min_priority,
       listing.priority   AS priority,
       listing.value      AS value,
       listing.file_id    AS file_id
  FROM listing,
       (
         SELECT min(min_p_value.min_priority) AS min_priority,
                min_p_value.value_id          AS min_value_id,
                listing.file_id               AS file_id
           FROM listing,
                (
                  SELECT min(listing.priority) AS min_priority,
                         listing.value         AS value,
                         listing.value_id      AS value_id
                    FROM listing
                   GROUP BY listing.value, listing.value_id
                ) min_p_value
          WHERE listing.value = min_p_value.value
            AND listing.value_id = min_p_value.value_id
            AND min_p_value.min_priority = min_priority
          GROUP BY min_p_value.value_id, listing.file_id
       ) min_p
 WHERE min_p.min_value_id = listing.value_id
   AND min_p.file_id = listing.file_id
 ORDER BY min_p.min_priority ASC,
          listing.value ASC,
          listing.value_id ASC,
          listing.priority;

And this returns the following incorrect result:

 MIN_PRIORITY   PRIORITY      VALUE    FILE_ID
------------- ---------- ---------- ----------
           10         10        654        444
           10         20        654        444
           10         10        876        333
           10         20        876        222 <-- incorrect, should have a min_priority of 20, and therefore be the last
           10         10        987        111
           10         20        987        111
           20         20        765        111

How can I achieve what I expect?

user_3515341 · Answer

this should work:

select (select min(priority)
          from main_t mm
          join priority_t tt
            on tt.id = mm.priority_id
         where mm.value_id = m.value_id) as min_priority,
       p.priority as priority,
       v.value as value,
       m.value_id,
       m.file_id
  from main_t m
  join priority_t p
    on p.id = m.priority_id
  join value_t v
    on v.id = m.value_id
 order by 1, 3, 4, 2;

It determines the minimum priority by value_id. You can order the result by column number as shown.

user_8211528 · Answer

I have written this code in MySQL (not Oracle) and was not sure about the CTE syntax, so I replaced the WITH clause with a temp table called min_priority, but you can of course replace the temp table with a CTE. When I ran this on MySQl, I got the same result as you wanted.

Also was not sure whether you need a left join or inner join, both would work in this example.

-- find min priority for each value
create temporary table if not exists min_priority as (
select m.value_id, min(p.priority) as min_pri
from main_t m
inner join priority_t p on m.priority_id = p.id
group by m.value_id
);

-- just join all the tables, including min_priority, and order the result
select mp.min_pri, p.priority, v.value, v.id, f.id
from main_t m
left join priority_t p on m.priority_id = p.id
left join value_t v on m.value_id = v.id
left join file_t f on m.file_id = f.id
left join min_priority mp on mp.value_id = v.id
order by mp.min_pri asc, v.value asc, v.id asc, p.priority asc;

Just as a side note, I wouldn't use multiple levels of inner queries, as your example in the question, as they would affect the performance.

user_4444980 · Answer

When you need aggregated and non-aggregated values use analytic functions. I think, that would be something like this:

select *
  from (select min(p.priority) over (partition by v.id, v.value, f.id) min_priority, 
               p.priority, v.value, v.id value_id, f.id file_id 
          from main_t m
          join priority_t p on m.priority_id = p.id
          join value_t v    on m.value_id = v.id
          join file_t f     on m.file_id = f.id)
  order by min_priority, value, value_id, priority

Result:

MIN_PRIORITY    PRIORITY       VALUE    VALUE_ID     FILE_ID
------------ ----------- ----------- ----------- -----------
          10          10         654           4         444
          10          20         654           4         444
          10          10         876           2         333
          10          10         987           1         111
          10          20         987           1         111
          20          20         765           3         111
          20          20         876           2         222

Grouping values in order to sort by minimum of a value, then other fields then by that value itself

3 Answers