Palabra key SQLite Exists: ¿cómo consultar el promedio más alto?

En una tabla de database SQLite con dos columnas 'mID' y 'estrellas', tengo que devolver 'mID's con los valores promedio más altos de' estrellas '.

Tener los siguientes datos:

  Clasificación
 estrellas mID
 101 2
 101 4
 106 4
 103 2
 108 4
 108 2
 101 3
 103 3
 104 2
 108 4
 107 3
 106 5
 107 5
 104 3

Primero tomaría un promedio de 'estrellas' de cada 'mID' al agruparlo por 'mID', como

select mID, avg(stars) theAvg from Rating group by mID; 

Como resultado, obtendría la tabla de valores promedio de "estrellas" para cada "mID".

  mID avg (estrellas)
 101 3.0
 103 2.5
 104 2.5
 106 4.5
 107 4.0
 108 3.33333333333

Si tuviera que devolver el valor promedio más alto de 'estrellas',
entonces podría haber tomado algo como select max (theAvg) seguido de lo que acabo de calcular.
Pero luego, para get las 'estrellas' promedio más altas asociadas con su 'mID', necesitaba algo más.

Así que utilicé la palabra key 'no existe' seguida de una subconsulta que genera otra tabla de 'mID' y 'estrellas'. Esta subconsulta se compara con la tabla original para verificar que para un valor promedio de "estrellas" de la tabla original R1, no existe un valor promedio de "estrellas" de la nueva tabla R2 que sea mayor que el valor "estrellas" promediado de R1

 select mID, theAvg from (select mID, avg(stars) theAvg from Rating group by mID) as R1 where not exists(select * from (select mID, avg(stars) theAvg from Rating group by mID) as R2 where R2.theAvg > R1.theAvg); 

Pensé que como resultado de esta consulta, obtendría las estrellas promedio más altas y es mID, pero en cambio lo que obtengo son dos tuplas ('mID': 106, 'theAvg': 4.5) y ('mID': 107, ' theAvg ': 4.0), cuando la respuesta deseada es solo una tupla (' mID ': 106,' theAvg ': 4.5), ya que estamos buscando el promedio más alto de todos los promedios de' estrellas '.

  El resultado de mi consulta (incorrecto):
 mID theAvg
 106 4.5
 107 4.0

 El resultado deseado:
 mID theAvg
 106 4.5

¿Qué pasos crees que me equivoqué? ¿Alguna sugerencia de cómo lo harías?

Puede order by desc en promedio y agregar una cláusula de limit como se muestra aquí:

 select mID, avg(stars) theAvg from Rating group by mID order by theAvg desc limit 1; 

Debería darte esto:

 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 

Documentación de esta manera: http://www.sqlite.org/lang_select.html#orderby

Lo siento, soy un poco nuevo en SQL y SO, pero encontré una solución que funciona cuando hay un empate para la mayor promedio (estrellas) (o más especialmente una cantidad desconocida de vínculos, en cuyo caso no se puede configurar fácilmente un límite para la salida orderada). Como dije, soy un poco nooby así que es un poco desorderado:

 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 R1 join (select avg(stars) theAvg, mID, ratingDate from Rating group by mID) as R2 where R1.theAvg < R2.theAvg) group by mID; 

La subconsulta devuelve el mID de cualquier película que tenga estrellas promedio less que las estrellas promedio de cualquier otra película, y la instrucción where de la consulta principal toma cualquier mID que no devolvió la subconsulta. La lógica es bastante similar a la que tenías originalmente.

En cuanto al problema de las dos tuplas: realmente no entiendo dónde saliste mal, pero te avisaré si lo averiguo.