SQLite Exists keyword : How to query the highest average?

You can order by desc on the average, and add a limit clause as shown here:

select mID, avg(stars) theAvgfrom Ratinggroup by mIDorder by theAvg desc limit 1;

Should give you this:

sqlite> create table Rating (mID INT, stars INT);sqlite> sqlite> insert into Rating values (101, 2);sqlite> insert into Rating values (101, 4);sqlite> insert into Rating values (106, 4);sqlite> insert into Rating values (103, 2);sqlite> insert into Rating values (108, 4);sqlite> insert into Rating values (108, 2);sqlite> insert into Rating values (101, 3);sqlite> insert into Rating values (103, 3);sqlite> insert into Rating values (104, 2);sqlite> insert into Rating values (108, 4);sqlite> insert into Rating values (107, 3);sqlite> insert into Rating values (106, 5);sqlite> insert into Rating values (107, 5);sqlite> insert into Rating values (104, 3);sqlite> sqlite> select mID, avg(stars) theAvg   ...> from Rating   ...> group by mID   ...> order by theAvg DESC LIMIT 1;106|4.5

Documentation this way: http://www.sqlite.org/lang_select.html#orderby

Sorry I'm a somewhat new to SQL and SO, but I found a solution that works when there is a tie for highest avg(stars) (or more specially an unknown amount of ties, in which case you can't easily set a limit for the ordered output). Like I said, I'm kinda nooby so its a little messy:

select title, avg(stars)from movie join rating using(mID)where mID not in (select R1.mID  from (select avg(stars) theAvg, mID, ratingDate from Rating group by mID) as R1join (select avg(stars) theAvg, mID, ratingDate from Rating group by mID)as R2 where R1.theAvg < R2.theAvg)group by mID;

The subquery returns the mID of any movie that has average stars less than any other movie's average stars, and the main query's where statement takes any mID the subquery didn't return. The logic is pretty similar to what you had originally.

As for the two tuple problem: I don't really understand where you went wrong, but I'll let you know if I figure it out.