Select Highest COUNT()

[grecorromano]

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.

[justsomeguy]

It's going to select the max Y for each X. If you only want the highest Y, order by max Y and use limit to only get one row.

[grecorromano]

ok, but if i have more than one people with 4.For exaple, if i have more than one people with 4 i want it to show:4 John Ford4 George Smith4 Mary Falcoetc.

[justsomeguy]

In that case you need a WHERE clause, where you tell it to only return rows where Y is MAX(Y).

[grecorromano]

Group functions are not allowed in WHERE clause

[justsomeguy]

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.

[grecorromano]

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!