Clever Geek Handbook

SELECT rows with minimum number of samples (*)

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

id(primaryKey),token(int),candidate(int),rank(int).

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

+3


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;
+1


source


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 .....)

0


source


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

0


source






More articles:


All Articles