Get more entries that appear more than once

Question

How can I see all the entries that appear more than once a day?

I have this table:

ID   Name     Date
1    John     27.03.2010 18:17:00
2    Mike     27.03.2010 16:38:00
3    Sonny    28.03.2010 20:23:00
4    Anna     29.03.2010 13:51:00
5    Maria    29.03.2010 21:59:00
6    Penny    29.03.2010 17:25:00
7    Alba     30.03.2010 09:36:00
8    Huston   31.03.2010 10:19:00

I want to receive:

1    John     27.03.2010 18:17:00
2    Mike     27.03.2010 16:38:00
4    Anna     29.03.2010 13:51:00
5    Maria    29.03.2010 21:59:00
6    Penny    29.03.2010 17:25:00

Answer 1

You can append to the date part a date variable that excludes any where there is only one row for that date (because there is nothing to add to it).

This example is in t-sql:

select  distinct l.*
from    @table l
join 
    @table r
on  convert(varchar, l.[date], 102) = convert(varchar, r.[date],102)
and l.id != r.id 

Answer 2

This should work if you are using MySQL.

SELECT *
FROM `thetable`
GROUP BY DATE(`Date`)
HAVING COUNT(*) > 1

I have not tested this, but this is the very first thing I can come up with.

date DateTime ( , , . . Date backticks, Date MySQL ( ).

, RDBMS .

Answer 3

Mytable Date somedate, :

--create table mytable(ID int,Name varchar(32), somedate datetime)
select *
from mytable
where id in (
select id
from mytable
group by convert(varchar(10), somedate, 101), id
having count(1) > 1
)

Answer 4

, " , ?"

select *
from your_table
where trunc(date) in ( select trunc(date)
                       from your_table
                       group by trunc(date)
                       having count(*) > 1)
/

, SQL Server. ORACLE TRUNC(), . -, SQL Server , .

Answer 5

UPDATE: SQL Server:

CREATE TABLE t1 (id int IDENTITY PRIMARY KEY, name varchar(20), date datetime);

INSERT INTO t1 VALUES('John',   '2010-03-27 18:17:00');
INSERT INTO t1 VALUES('Mike',   '2010-03-27 16:38:00');
INSERT INTO t1 VALUES('Sonny',  '2010-03-28 20:23:00');
INSERT INTO t1 VALUES('Anna',   '2010-03-29 13:51:00');
INSERT INTO t1 VALUES('Maria',  '2010-03-29 21:59:00');
INSERT INTO t1 VALUES('Penny',  '2010-03-29 17:25:00');
INSERT INTO t1 VALUES('Alba',   '2010-03-30 09:36:00');
INSERT INTO t1 VALUES('Huston', '2010-03-31 10:19:00');

SELECT  t1.id, t1.name, sub_t.date
FROM    t1
JOIN    (SELECT   DATEADD(dd, DATEDIFF(dd,0, date), 0) as date
         FROM     t1 
         GROUP BY DATEADD(dd, DATEDIFF(dd,0, date), 0) 
         HAVING   COUNT(id) > 1) sub_t ON 
                  (sub_t.date = DATEADD(dd, DATEDIFF(dd,0, t1.date), 0));

:

+----+-------+---------------------+
| id | name  | date                |
+----+-------+---------------------+
|  1 | John  | 2010-03-27 00:00:00 |
|  2 | Mike  | 2010-03-27 00:00:00 |
|  4 | Anna  | 2010-03-29 00:00:00 |
|  5 | Maria | 2010-03-29 00:00:00 |
|  6 | Penny | 2010-03-29 00:00:00 |
+----+-------+---------------------+

---

: MySQL:

:

CREATE TABLE t1 (id int AUTO_INCREMENT PRIMARY KEY, 
                 name varchar(20), 
                 date datetime);

INSERT INTO t1 VALUES(NULL, 'John',   '2010-03-27 18:17:00');
INSERT INTO t1 VALUES(NULL, 'Mike',   '2010-03-27 16:38:00');
INSERT INTO t1 VALUES(NULL, 'Sonny',  '2010-03-28 20:23:00');
INSERT INTO t1 VALUES(NULL, 'Anna',   '2010-03-29 13:51:00');
INSERT INTO t1 VALUES(NULL, 'Maria',  '2010-03-29 21:59:00');
INSERT INTO t1 VALUES(NULL, 'Penny',  '2010-03-29 17:25:00');
INSERT INTO t1 VALUES(NULL, 'Alba',   '2010-03-30 09:36:00');
INSERT INTO t1 VALUES(NULL, 'Huston', '2010-03-31 10:19:00');

SELECT  t1.id, t1.name, sub_t.date
FROM    t1
JOIN    (SELECT   DATE(date) as date
         FROM     t1 
         GROUP BY DATE(date) 
         HAVING   COUNT(id) > 1) sub_t ON (sub_t.date = DATE(t1.date));

:

+----+-------+------------+
| id | name  | date       |
+----+-------+------------+
|  1 | John  | 2010-03-27 |
|  2 | Mike  | 2010-03-27 |
|  4 | Anna  | 2010-03-29 |
|  5 | Maria | 2010-03-29 |
|  6 | Penny | 2010-03-29 |
+----+-------+------------+
5 rows in set (0.02 sec)

Answer 6

  Select *  
    From YourTable  
   Where ADate in (Select ADate  
                     From YourTable  
                    Group By ADate  
                   Having Count(Distinct Id) > 1  
                 )  

Answer 7

:

SELECT * 
FROM   theTable t1 
WHERE  t1.created_at IN 
       (SELECT   t2.created_at 
        FROM     theTable t2 
        GROUP BY created_at 
        HAVING COUNT(*) > 1)

Answer 8

:

SELECT * 
FROM   table 
WHERE  convert(varchar, table.DataInput, 102) IN 
       (SELECT convert(varchar, table.DataInput, 102) 
        FROM table 
        GROUP BY convert(varchar, table.DataInput, 102) 
        HAVING COUNT(*) > 2)

!:)