0

The task: What ingredients has the maximum amount of content in Pancake?

The tables:
Recipe (Name, group)
Ingredient (Iname, price)
Composition (Name, Iname, Amount)

I tried this query:

Select Iname, Amount
From Composition
Where Name='Pancake'
Ordered by Amount ASC

But it does not return the correct result. What's wrong with it?

3
  • 2
    It's order by not ordered by
    – user1822
    Commented Jun 19, 2016 at 21:38
  • 1
    en.wikipedia.org/wiki/Select_(SQL)
    – Andriy M
    Commented Jun 19, 2016 at 21:55
  • 1
    Please edit your question to say what, specifically, is "got good" and how you would like it to improve. Commented Jun 19, 2016 at 22:59

2 Answers 2

2

The typo in ORDER BY aside, you need descending sort order to get the ingredient with the maximum amount first.

SELECT Iname, Amount
FROM   Composition
WHERE  Name = 'Pancake'
ORDER  BY Amount DESC
FETCH  FIRST 1 ROWS ONLY;

Assuming Amount is defined NOT NULL.

FETCH FIRST 1 ROWS ONLY is the syntax defined by the SQL standard and supported by several RDBMS, but not all. Depending on your undisclosed RDBMS you may need a different syntax variant. Here is a pretty complete list:

https://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause

0

Here is your query, you need to join Ingredient i with Composition c on i.iname=c.iname with 2 condition name='Pancake' and where amount >= select max(amount) from Composition where name='Pancake'.

SELECT i.Iname, Amount 
FROM Ingredient i INNER JOIN Composition c ON i.iName=c.iName
WHERE Name='Pancake'
AND Amount >= (SELECT MAX(Amount) FROM Composition WHERE Name='Pancake');

Not the answer you're looking for? Browse other questions tagged or ask your own question.