Complex 'order by' or maybe sorting issue

Question

I have a table that looks like this

Group  Recipe    Priority
0       A         400
0       A         200
0       B         500
0       B         100
1       C         300
1       C         300
1       D         600

Importance is "Group" > "Priority" > "Recipe"

Group 0 has to go first. Within Group 0, Priority 500 has to go first (since it has higher priority), but for the efficiency, all the recipe has to go first.

After sorting, it should look like this

Group  Recipe    Priority
0       B         500
0       B         100
0       A         400
0       A         200
1       D         600
1       C         300
1       C         300

I have tried all different ways to do 'order by' but cannot find a correct way.

thank you for the help

Answer 1

In older versions of Oracle, prior to having analytic functions available, we would have returned the specified result set using a query something like this:

SELECT f.group
     , f.recipe
     , f.priority
  FROM foo f
  JOIN ( SELECT g.group
              , g.recipe
              , MAX(g.priority) AS max_priority 
           FROM foo g
          GROUP BY g.group, g.recipe
       ) m
    ON m.group = f.group AND m.recipe = f.recipe
 ORDER BY f.group
        , m.max_priority DESC
        , f.recipe
        , f.priority DESC

This approach works in other databases that don't have analytic functions, such as MySQL.

NOTE: The query above is not NULL-safe, in that the JOIN predicates will eliminate rows that have NULL values for the group or recipe columns. It could be made NULL-safe, but it complicates the SQL a bit.

SELECT f.group
     , f.recipe
     , f.priority
  FROM foo f
  JOIN ( SELECT g.group
              , g.recipe
              , MAX(g.priority) AS max_priority 
           FROM foo g
          GROUP BY g.group, g.recipe
       ) m
    ON (m.group = f.group OR COALESCE(m.group,f.group) IS NULL)
       AND (m.recipe = f.recipe OR COALESCE(m.recipe,f.recipe) IS NULL)
 ORDER BY f.group
        , m.max_priority DESC
        , f.recipe
        , f.priority DESC

An equivalent result can also be obtained using a correlated subquery in the SELECT list, except that this result set contains an extra "max_priority" column in the result set.

SELECT f.group
     , f.recipe
     , f.priority
     , (SELECT MAX(g.priority)
          FROM foo g
         WHERE (g.group = f.group OR COALESCE(g.group,f.group) IS NULL)
           AND (g.recipe = f.recipe OR COALESCE(g.recipe,f.recipe) IS NULL)
       ) AS max_priority
  FROM foo f
 ORDER BY f.group
        , 4 DESC
        , f.recipe
        , f.priority DESC

(I haven't tested whether that correlated subquery could be removed from the SELECT list and entirely moved to the ORDER BY clause. If that worked, we'd eliminate returning the extra column, but that query would look really, really odd.) The other option (to omit that extra column) is to wrap this query (as an inline view) in another query.

SELECT e.group
     , e.recipe
     , e.priority 
  FROM (
        SELECT f.group
             , f.recipe
             , f.priority
             , (SELECT MAX(g.priority)
                  FROM foo g
                 WHERE (g.group = f.group OR COALESCE(g.group,f.group) IS NULL)
                   AND (g.recipe = f.recipe OR COALESCE(g.recipe,f.recipe) IS NULL)
                ) AS max_priority
          FROM foo f
       ) e
 ORDER BY e.group
        , e.max_priority DESC
        , e.recipe
        , e.priority DESC

Answer 2

The problem is subtle. You want to order not by priority, but by the maximum priority for a given group/recipe combination. That is, you want to keep all the recipes together based on this max priority.

The following does this:

select t.Group, t.Recipe, t.Priority,
       max(priority) over (partition by t.group, t.recipe) as maxpriority
from tablename t
order by t.Group asc, 4 desc, t.Recipe, priority desc