How to select a specific row from count table in SQL

Question

Is that possible to select a specific row from a group count?

This is my sql statement:

SELECT
    COUNT(*)
FROM
    khashrm a
INNER JOIN price_name b WHERE
    (a.type3 = b.name)
GROUP BY
    a.type3
ORDER BY
    `COUNT(*)`
DESC
    

and the result will be :

+---------+
| COUNT() |
+---------+
| 1       |
+---------+
| 5       |
+---------+
| 10      |
+---------+

I want to select only that row which held 10, BTW this row is changeable, it’s not fixed value.

Answer 1

If you want the row whose count is 10, use having:

SELECT COUNT(*)
FROM khashrm a
INNER JOIN price_name b ON a.type3 = b.name
GROUP BY a.type3
HAVING COUNT(*) = 10
ORDER BY COUNT(*) DESC

If you want the third row in the resultset, then that's limit and offset:

SELECT COUNT(*)
FROM khashrm a
INNER JOIN price_name b ON a.type3 = b.name
GROUP BY a.type3
ORDER BY COUNT(*) DESC
LIMIT 1 OFFSET 2

Notes:

• use standard joins; the join condition goes to the on clause rather than to the where clause

• no need for parentheses around the join condition