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 several times. How to select only dates that appear <k number of times?

Answer 1

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

Hope it works for ORACLE. NTN

Answer 2

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

See also answer [SQLMenace]. This is very similar to this, but depending on your database, its JOIN will probably be faster if the optimizer doesn't make a difference.

Answer 3

Use the COUNT aggregate:

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

Answer 4

For "x times" appears, it is best to use the HAVING clause. In your case, the query might look like this:

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

or, you need to select columns other than Date:

 SELECT * FROM Table WHERE Date IN ( SELECT Date FROM table GROUP BY Date HAVING COUNT(*)<k) 

You can also rewrite IN to an INNER JOIN, however, this will not give a performance gain, since in fact the query optimizer will do this for you in most DBMSs. Having an index by date will certainly improve performance for this query.

Answer 5

Example

 DECLARE @Max int SELECT @Max = 5 SELECT t1.* FROM [MyTable] t1 JOIN( SELECT [Date] FROM [MyTable] GROUP By [Date] HAVING COUNT(*) < @Max ) t2 on t1.[Date] = t2.[Date] 

Answer 6

 SELECT date, COUNT(date) FROM table GROUP BY date HAVING COUNT(date) < k 

And then return the original data:

 SELECT table.* FROM table INNER JOIN ( SELECT date, COUNT(date) FROM table GROUP BY date HAVING COUNT(date) < k) dates ON table.date = dates.date 

Answer 7

Assuming you are using Oracle, and k = 5: -

 select date_col,count(*) from your_table group by date_col having count(*) < 5; 

If time is added in the date column and you want to ignore it, change the query so that it looks like this: -

 select trunc(date_col) as date_col,count(*) from your_table group by trunc(date_col) having count(*) < 5; 

Answer 8

You may not be able to count directly on the date field if your dates include time. You may need to first convert to year / month / day format, and then make an account.

Otherwise, your accounts will be disconnected, as usual, at the same time very few records.