SQL Query Help: Selecting Rows That Appear A Certain Number Of Times

Question

I have a table with a "Date" column. Each Date may appear multiple times. How do I select only the dates that appear < k number of times?

Answer 1

SELECT * FROM [MyTable] WHERE [Date] IN
(
    SELECT [Date] 
    FROM [MyTable] 
    GROUP By [Date] 
    HAVING COUNT(*) < @Max
)

See @[SQLMenace] 's response also. It's very similar to this, but depending on your database his JOIN will probably run faster, assuming the optimizer doesn't make the difference moot.

Answer 2

select dates 
  from table t 
 group by dates having count(dates) < k ;

Hopefully, it works for ORACLE. HTH

Answer 3

Use the COUNT aggregate:

SELECT Date
FROM SomeTable
GROUP BY Date
HAVING COUNT(*) < @k