Having count condition with greater than and less than for Sybase DB

Question

I am using below query in sybase db, i need data to show where count(Field1 >=2 and Field1 <=5) Select * FROM Table1 WHERE Field1 IN( SELECT Field1 FROM Table1 GROUP BY Field1 HAVING COUNT(Field1) >= 2 ORDER BY Field1How can i achieve this

Answer 1

You are basically there:

Select *
FROM Table1
WHERE Field1 IN (SELECT Field1
                 FROM Table1
                 GROUP BY Field1
                 HAVING COUNT(Field1) BETWEEN 2 AND 5
                )
ORDER BY Field1;

However, I would suggest window functions:

select t1.*
from (select t1.*, count(*) over (partition by field1) as cnt
      from table1 t1
     ) t1
where t1.cnt between 2 and 5;