MYSQL Sub-Query (SELECT MIN)

Asked

Viewed 181 times

1

I have this code, but what I want is the SELECT MIN "n_rowers" and only the min number. I wanted the club with fewer rowers. Help? :)

SELECT (clube.nome) AS clube,
       COUNT(remadores.cod_remador) AS n_remadores
FROM clube,
     remadores
WHERE clube.cod_clube=remadores.cod_clube
GROUP BY clube.nome
HAVING COUNT(remadores.cod_remador) >= ALL
  (SELECT COUNT(remadores.cod_remador) AS n_remadores
   FROM clube,
        remadores
   WHERE clube.cod_clube= remadores.cod_clube
   GROUP BY remadores.nome)
sql

  • Which database ?: and what exactly do you want ? the sub query min ? or just the min of the result ?//

  • Mysql. I want the output min (n_rowers)

  • you speak a line with the name of the club and the min of rowers.cod_rower ... or you are trying to get a line for each club with the min value of the sub query ?

  • SELECT (club.name) AS club, COUNT(rowers.cod_rower) AS n_rowers FROM club, rowers WHERE club.cod_club=rowers.cod_club GROUP BY club.name //I want the minimum result of this consultation!

  • Which bd solution changes according to bd , top sqlserver, limit mysql , rownum oracle etc.

  • phpMyAdmin, Mysql

Show 1 more comment

1 answer

0

Use temporary tables.

SELECT min(cod_remador) FROM
(SELECT (clube.nome) AS clube,
       COUNT(remadores.cod_remador) AS n_remadores
FROM clube,
     remadores
WHERE clube.cod_clube=remadores.cod_clube
GROUP BY clube.nome
HAVING COUNT(remadores.cod_remador) >= ALL
  (SELECT COUNT(remadores.cod_remador) AS n_remadores
    FROM clube,
        remadores
   WHERE clube.cod_clube= remadores.cod_clube
   GROUP BY remadores.nome)) AS Temp

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.