Postgres Nested Query for single table

Question

I am stuck on a nested database query. Can I get some help?

My simple table looks like this:

food_table:

+----+----------+-------------------------------+
| ID | NAME     | nutrientName  | NutrientAmount
+----+----------+---------------+---------------+
        food1       calcium            200
        food1       magnesium          300
        food1       phosphorus         400
        food2       calcium            220
        food2       magnesium          320
        food2       phosphorus         430
        food3       calcium            230
     .............

I used this to get top 15 nutrition amount:

select NAME from food_table
    where nutrientName in ('calcium','magnesium')
        group by NAME
     order by sum(nutrient_amount) desc
limit 15;

Currently, I only have NAME. But, I want to get the all nutrition of the food and with the same order. If I do another select, the order will be lost. Is there a way to get the food records and with the same order?

Edit: I've created a fiddle:

https://www.db-fiddle.com/f/2HkVZPbTxkRNaNpDjT35iM/3

Answer 1

One way to do this uses window functions:

select ft.*
from (select ft.*,
             row_number() over (partition by name order by nutrient_amount desc) as seqnum
      from food_table ft
      where nutrientName in ('calcium','magnesium')
     ) ft
where seqnum = 1
order by nutrient_amount desc;

The above returns the nutrients by the highest amount for a single nutrient. If you want it for both, then:

select ft.*
from (select ft.*,
             sum(nutrient_amount) over (partition by name order by nutrient_amount desc) as sum_nutrient_amount
      from food_table ft
      where nutrientName in ('calcium', 'magnesium')
     ) ft
order by sum_nutrient_amount desc
limit 15;

Or if you just want the amounts, then conditional aggregation might be what you want:

select name,
       sum(case when nutrientName = 'calcium' then nutrient_amount else 0 end) as ca,
       sum(case when nutrientName = 'magnesium' then nutrient_amount else 0 end) as mg
from food_table
group by name
order by (ca + mg) desc
limit 15;