1

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. Error Message

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

Thank you

user1330649
  • 79
  • 5

3 Answers3

2

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)
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • But that doesn't produce the answer I want. That outputs the same as if i were to type `SELECT Rate, Rentalcode FROM RentalRates` I want the MIN rate, if you look at the output in the SQL Fiddle, that is exactly what i want – user1330649 Aug 20 '13 at 05:43
  • 1
    why do you want to get record `D` based on your records in the fiddle? it works in the fiddle since the rdbms you are using is `MySQL`. by the way it will yield different result when you have this set of records: http://sqlfiddle.com/#!2/be156/1 – John Woo Aug 20 '13 at 05:45
  • Sorry, I will edit and clarify my requirements a bit better in the question. – user1330649 Aug 20 '13 at 05:48
  • Sorry I didn't even realise. You are right, I don't want the record 'D' it should be WL :/ – user1330649 Aug 20 '13 at 05:54
  • i think this is the best way to get the lowest rate, http://sqlfiddle.com/#!2/be156/3 this supports multiple records having the lowest rate. – John Woo Aug 20 '13 at 06:02
0

It's not clear what you want to get with this query. I guess following will work:

SELECT Rate, Rentalcode
FROM RentalRates 
order by Rate 
LIMIT 1

SQLFiddle demo

valex
  • 23,966
  • 7
  • 43
  • 60
0

As you see, even in SQLfiddle your result are quite strange - you're getting RentalCode from one record and Rate from another.
Select aggregate from table without grouping is MySQL syntax and not ANSI. If you want to get record with minimum Rate, here's a query:

select * from RentalRates order by Rate limit 1

sql fiddle demo

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197