Jump to content

Select Highest COUNT()


grecorromano
 Share

Recommended Posts

Hi im trying to get the highest count of a row. here is what i'm doing:SELECT MAX(Y),X FROM(SELECT Pe.nombreArtistico AS X,COUNT(Po.id) AS YFROM Pelicula P, Persona Pe, Postulada Po, Premio Pr, TABLE(Pr.nombre) C, PERSONA_ROL_PELICULA PRP WHERE P.id=PRP.id_pelicula AND Pr.id=Po.id_premio AND PRP.id_postulada=Po.id AND Pe.id=PRP.id_persona AND Po.ganadora='si' AND C.nombre='Mejor Director' GROUP BY Pe.nombreArtistico ORDER BY COUNT(Po.id) DESC)GROUP BY X;but the output is:4 John Ford3 William Wylerit should be just:4 John Fordi'm using oracle.

Link to comment
Share on other sites

You could have a subquery, e.g. WHERE Y IN (SELECT MAX(Y) FROM table). It might also be easier to process that with another language, so get the results ordered by Y and keep printing them until you find one that is less than the max.

Link to comment
Share on other sites

i tryed this and it worked:SELECT Y,X FROM(SELECT Pe.nombreArtistico AS X,COUNT(Po.id) AS Y FROM Pelicula P, Persona Pe, Postulada Po, Premio Pr, TABLE(Pr.nombre) C, PERSONA_ROL_PELICULA PRP WHERE P.id=PRP.id_pelicula AND Pr.id=Po.id_premio AND PRP.id_postulada=Po.id AND Pe.id=PRP.id_persona AND Po.ganadora='si' AND C.nombre='Mejor Director' GROUP BY Pe.nombreArtistico ) WHERE Y=(SELECT MAX(Z) FROM (SELECT COUNT(Po.id) AS Z FROM Pelicula P, Persona Pe, Postulada Po, Premio Pr, TABLE(Pr.nombre) C, PERSONA_ROL_PELICULA PRP WHERE P.id=PRP.id_pelicula AND Pr.id=Po.id_premio AND PRP.id_postulada=Po.id AND Pe.id=PRP.id_persona AND Po.ganadora='si' AND C.nombre='Mejor Director' GROUP BY Pe.nombreArtistico ));thanks for the ideas, i've been working on this all day long!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...