CODEWITHSUNDEEP
mysqlsql
ruud
ruud

Reputation: 210

How to write sql query to get items from range

I would like to get values without the smallest and the biggest ones, so without entry with 2 and 29 in column NumberOfRepeating.

My query is:

SELECT Note, COUNT(*) as 'NumberOfRepeating'
  WHERE COUNT(*) <> MAX(COUNT(*))AND COUNT(*) <> MIN(COUNT(*))
   FROM Note GROUP BY Note;

Upvotes: 0

Views: 99

Answers (4)

Rajshakhar Paul Rajesh
Rajshakhar Paul Rajesh

Reputation: 41

Try this. It should work 

SELECT *
FROM ( SELECT Note, COUNT(*) as 'NumberOfRepeating'
      FROM Notes 
      GROUP BY Note
      ORDER BY NumberOfRepeating DESC
      LIMIT 1, 2147483647
    ) T1
ORDER BY T1.NumberOfRepeating
LIMIT 1, 2147483647

Upvotes: 0

Sagar R
Sagar R

Reputation: 613

   SELECT Note, COUNT(*) as 'NumberOfRepeating'
FROM Notes

GROUP BY Note
HAVING count(*) < 
(

SELECT max(t.maxi)
FROM (select
Note, COUNT(Note) maxi FROM Notes
 GROUP BY Note
 ) as t 
)

 AND
 count(*) >
(

SELECT min(t.min)
FROM (select
Note, COUNT(Note) min FROM Notes
 GROUP BY Note
 ) as t 
)

try this code.

Upvotes: 1

vibhav yadav
vibhav yadav

Reputation: 53

enter image description hereTry this code,

Select * from Note where NumberOfRepeating < (select MAX(NumberOfRepeating) from Note ) AND NumberOfRepeating > (select MIN(NumberOfRepeating) from Note );

Here in the code, as in your table Note is the name of the table, and NumberOfRepeating is the column name, as in your table.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

One method would use order by and limit, twice:

select t.*
from (select t.*
      from t
      order by NumberOfRepeating asc
      limit 99999999 offset 1
     ) t
order by NumberOfRepeating desc
limit 99999999 offset 1;

Upvotes: 0

Related Questions