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

I'm confused. Is the table named "food" or "recipe"? Also, add the expected result according to th example. — The Impaler
– The Impaler, Commented May 18, 2020 at 2:44
Please use CTE (with clause) rather than plaintext tables to specify your input data. Or prepare dbfiddle. It helps to concentrate on answer instead of text formatting. — Tomáš Záluský
– Tomáš Záluský, Commented May 18, 2020 at 6:37
@TomášZáluský Thanks! I've created a fiddle: db-fiddle.com/f/2HkVZPbTxkRNaNpDjT35iM/3 — kevinzf
– kevinzf, Commented May 18, 2020 at 20:31

Gordon Linoff · Accepted Answer · 2020-05-18 12:22:07Z

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;

Collectives™ on Stack Overflow

Postgres Nested Query for single table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related