Why won't this statement work? SQL

Question

I am using SQLFire. I am doing an assignment and when beginning the assignment I came across that I couldn't return a particular value. See this SQL Fiddle, it contains the EXACT data in the table that I am using in my assignment and it contains the SQL Statement that i have tried. FIDDLE: http://sqlfiddle.com/#!2/e761ac/1

What i want to be outputted is:

Rate | RentalCode
-----------------
350  |    WL

I am getting this error when I type my code into SQL Fire.

I Have been told NOT to use the ORDER BY clause and I have not learnt 'LIMIT'

Thank you

Answer 1

You need to have GROUP BY clause since you have non-aggregated column in the SELECT clause

SELECT MIN(Rate), Rentalcode
FROM RentalRates 
GROUP BY Rentalcode

UPDATE

Since you want to get the lowest rate, I think this is the better way than using ORDER BY - LIMIT since it supports multiple records having the lowest rate.

SELECT *
FROM RentalRates
WHERE rate = (SELECT MIN(rate) FROM rentalrates)