# 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

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 like`HAVING count > 10`

0

source

All Articles