SELECT rows with minimum number of samples (*)

Let's say I have a simple tabular voting with columns



I want to extract all rows with a specific rank, grouped by candidate and most importantly, only the minimum score (*). So far I have reached

SELECT candidate, count( * ) AS count
FROM voting
WHERE rank =1
AND candidate <200
GROUP BY candidate
HAVING count = min( count )


But it returns an empty set. If I replace min (count) with the actual minimum value, it works correctly. I have also tried

SELECT candidate,min(count)
FROM (SELECT candidate,count(*) AS count
      FROM voting
      where rank = 1
      AND candidate < 200
      group by candidate
      order by count(*)
      ) AS temp


But this only resulted in 1 line, I have 3 lines with the same number of minutes but with different candidates. I need all these 3 lines.

Can anyone help me. The number of rows with the same count value (*) will also help.

The example is quite large, so I am showing some dummy values

1 $sampleToken1 101 1

2 $sampleToken2 102 1

3 $sampleToken3 103 1

4 $sampleToken4 102 1


Here, when grouped by candidate, there are 3 lines concatenating count (*) results

candidate count( * )

101              1

103              1

102              2


I want the top two rows to be displayed with index (*) = 1 or any minimum value


source to share

3 answers

Try using this script template like <->

-- find minimum count
SELECT MIN(cnt) INTO @min FROM (SELECT COUNT(*) cnt FROM voting GROUP BY candidate) t;

-- show records with minimum count
SELECT * FROM voting t1
  JOIN (SELECT id FROM voting GROUP BY candidate HAVING COUNT(*) = @min) t2
    ON t1.candidate = t2.candidate;




Remove your HAVING keyword entirely, it is misspelled.

and add SUB SELECT to the where clause to meet these criteria.

(i.e. select cand, count (*) as the score from the vote, where rank = 1 and count = (select .....)



The HAVING keyword cannot use the MIN function the way you are trying. Replace MIN function with absolute value likeHAVING count > 10



All Articles