PottaG PottaG - 7 months ago 6
SQL Question

MySQL query that will leave out results that were repeated at least and most times

I need a MySQL query to get the results where there will not be mostly and least repeated values.

What that means?

If this MySQL query:

SELECT Ocjena,COUNT(*) FROM ocjenjivanje GROUP BY Ocjena;


returns:

enter image description here

I need query which will give me the next results:

Ocjena
1
3
5


Thanks

Answer

try this:

SELECT o.*
FROM (SELECT Ocjena, COUNT(*) AS c FROM ocjenjivanje GROUP BY Ocjena) AS o
INNER JOIN (SELECT Min(c) AS l, Max(c) AS h FROM  (SELECT Ocjena, COUNT(*) AS c FROM ocjenjivanje GROUP BY Ocjena)) AS m
ON o.c <> m.l AND o.c <> m.h
----OR
--ON o.c NOT IN(m.l, m.h)

or you can use this query:

SELECT Ocjena, c
FROM
( SELECT o.*, @min:=IF(@min > o.c, o.c, @min) AS l, @max:=IF(@max < o.c, o.c, @max) AS h
  FROM (SELECT Ocjena, COUNT(*) AS c FROM ocjenjivanje GROUP BY Ocjena) AS o
  , (select @max:=0, @min:=32000) AS m) AS x
WHERE c NOT IN(l, h)