Postgres Nested Query for a 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     | Nutrient      | NutrientAmount
+----+----------+---------------+---------------+
        food1       calcium            200
        food1       magnesium          300
        food1       phosphorus         400
        food2       calcium            220
        food2       magnesium          320
        food2       phosphorus         430
        food3       calcium            230
         .............

I want to select top 15 foods that have the most calcium and magnesium. It doesn't matter calcium or magnesium has the most.

I tried to use order by, but it doesn't work because it is to order a column. The data that I want to sort is stored in different rows.

I am new to database design. If schema has problem, I am am to change the schema also.

Gordon Linoff · Accepted Answer · 2020-05-17 12:16:28Z

1

This is tricky. You can do this using GROUP BY as:

select name
from food_table
where Nutrient in ('calcium', 'magnesium' )
group by name
order by max(NutrientAmount) desc 
limit 15;

If you wanted to consider both nutrients together, you would use sum() rather than max().

answered May 17, 2020 at 12:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kevinzf Over a year ago

Thanks. Is there a way to get the whole row rather than name? I tried to another select, but the order is lost.

Gordon Linoff Over a year ago

@kevinzf . . . That is actually trickier than it seems. I would suggest that you ask a new question.

ScaisEdge · Accepted Answer · 2020-05-17 06:54:16Z

1

You could try using distinct

select distinct NAME
from food_table
where Nutrient in ('calcium', 'magnesium' )
order by NutrientAmount DESC 
LIMIT 15

or for avoid distinct limit issue you can try using

select distinct t.name 
from (
    select NAME
    from food_table
    where Nutrient in ('calcium', 'magnesium' )
    order by NutrientAmount DESC 
) t
LIMIT 15

edited May 17, 2020 at 6:54

answered May 17, 2020 at 6:24

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

1 Comment

kevinzf Over a year ago

Thanks. I got an error: [42P10] ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list My query is: select distinct recipe_title from recipe where nutrient_id in (1, 2,3) order by nutrient_amount DESC LIMIT 15

Collectives™ on Stack Overflow

Postgres Nested Query for a single table

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related