<p>I need to return a list of record id's from a table that may/may not have multiple entries with that record id on the same date. The same date criteria is key - if a record has three entries on 09/10/2008, then I need all three returned. If the record only has one entry on 09/12/2008, then I don't need it.</p>

[{'answer_id': 76783, 'author': 'Leigh Caldwell', 'author_id': 3267, 'author_profile': 'https://Stackoverflow.com/users/3267', 'pm_score': 3, 'selected': False, 'text': '<pre><code>SELECT id, datefield, count(*) FROM tablename GROUP BY datefield\n  HAVING count(*) &gt; 1\n</code></pre>\n'}, {'answer_id': 76785, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p><code>GROUP BY</code> with <code>HAVING</code> is your friend:</p>\n\n<pre><code>select id, count(*) from records group by date having count(*) &gt; 1\n</code></pre>\n'}, {'answer_id': 76798, 'author': 'Manu', 'author_id': 2133, 'author_profile': 'https://Stackoverflow.com/users/2133', 'pm_score': 1, 'selected': False, 'text': '<pre><code>select id from tbl where date in\n(select date from tbl group by date having count(*)&gt;1)\n</code></pre>\n'}, {'answer_id': 76799, 'author': 'easeout', 'author_id': 10906, 'author_profile': 'https://Stackoverflow.com/users/10906', 'pm_score': 1, 'selected': False, 'text': '<p>For matching on just the date part of a Datetime:</p>\n\n<pre><code>select * from Table\nwhere id in (\n    select alias1.id from Table alias1, Table alias2\n    where alias1.id != alias2.id\n        and datediff(day, alias1.date, alias2.date) = 0\n)\n</code></pre>\n\n<p>I think.  This is based on my assumption that you need them on the same day month and year, but not the same time of day, so I did not use a Group by clause.  From the other posts it looks like I could have more cleverly used a Having clause.  Can you use a having or group by on a datediff expression?</p>\n'}, {'answer_id': 76815, 'author': 'Travis', 'author_id': 7316, 'author_profile': 'https://Stackoverflow.com/users/7316', 'pm_score': 1, 'selected': False, 'text': "<p>If I understand your question correctly you could do something similar to:</p>\n\n<pre><code>select\n     recordID\nfrom\n    tablewithrecords as a\n    left join (\n        select\n          count(recordID) as recordcount\n        from\n          tblwithrecords\n        where\n          recorddate='9/10/08'\n     ) as b on a.recordID=b.recordID\nwhere\n     b.recordcount&gt;1\n</code></pre>\n"}, {'answer_id': 76825, 'author': 'JBB', 'author_id': 12332, 'author_profile': 'https://Stackoverflow.com/users/12332', 'pm_score': 1, 'selected': False, 'text': '<p><a href="http://www.sql-server-performance.com/articles/dba/delete_duplicates_p1.aspx" rel="nofollow noreferrer">http://www.sql-server-performance.com/articles/dba/delete_duplicates_p1.aspx</a> will get you going. Also, <a href="http://en.allexperts.com/q/MS-SQL-1450/2008/8/SQL-query-fetch-duplicate.htm" rel="nofollow noreferrer">http://en.allexperts.com/q/MS-SQL-1450/2008/8/SQL-query-fetch-duplicate.htm</a></p>\n\n<p>I found these by searching Google for \'sql duplicate data\'. You\'ll see this isn\'t an unusual problem.</p>\n'}, {'answer_id': 76840, 'author': 'Sparr', 'author_id': 13675, 'author_profile': 'https://Stackoverflow.com/users/13675', 'pm_score': 1, 'selected': False, 'text': '<pre><code>SELECT * FROM the_table WHERE ROW(record_id,date) IN \n  ( SELECT record_id, date FROM the_table \n    GROUP BY record_id, date WHERE COUNT(*) &gt; 1 )\n</code></pre>\n'}, {'answer_id': 76849, 'author': 'Scott Nichols', 'author_id': 4299, 'author_profile': 'https://Stackoverflow.com/users/4299', 'pm_score': 2, 'selected': False, 'text': "<p>Since you mentioned needing all three records, I am assuming you want the data as well.  If you just need the id's, you can just use the group by query.  To return the data, just join to that as a subquery</p>\n\n<pre><code>select * from table\ninner join (\n     select id, date\n     from table \n     group by id, date \n     having count(*) &gt; 1) grouped \n       on table.id = grouped.id and table.date = grouped.date\n</code></pre>\n"}, {'answer_id': 76875, 'author': 'jkramer', 'author_id': 12523, 'author_profile': 'https://Stackoverflow.com/users/12523', 'pm_score': 1, 'selected': False, 'text': '<p>I\'m not sure I understood your question, but maybe you want something like this:</p>\n\n<pre><code>SELECT id, COUNT(*) AS same_date FROM foo GROUP BY id, date HAVING same_date = 3;\n</code></pre>\n\n<p>This is just written from my mind and not tested in any way. Read the GROUP BY and HAVING section <a href="http://dev.mysql.com/doc/refman/5.0/en/select.html" rel="nofollow noreferrer">here</a>. If this is not what you meant, please ignore this answer.</p>\n'}, {'answer_id': 76879, 'author': 'Chris Wuestefeld', 'author_id': 10082, 'author_profile': 'https://Stackoverflow.com/users/10082', 'pm_score': 1, 'selected': False, 'text': "<p>Note that there's some extra processing necessary if you're using a SQL DateTime field. If you've got that extra time data in there, then you can't just use that column as-is. You've got to normalize the DateTime to a single value for all records contained within the day. </p>\n\n<p>In SQL Server here's a little trick to do that:</p>\n\n<pre><code>SELECT CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS DATETIME)\n</code></pre>\n\n<p>You cast the DateTime into a float, which represents the Date as the integer portion and the Time as the fraction of a day that's passed. Chop off that decimal portion, then cast that back to a DateTime, and you've got midnight at the beginning of that day.</p>\n"}, {'answer_id': 76884, 'author': 'Eduardo Campañó', 'author_id': 12091, 'author_profile': 'https://Stackoverflow.com/users/12091', 'pm_score': 1, 'selected': False, 'text': '<pre>\nSELECT id, count(*)\nINTO #tmp\nFROM tablename\nWHERE date = @date\nGROUP BY id\nHAVING count(*) > 1\n\nSELECT *\nFROM tablename t\nWHERE EXISTS (SELECT 1 FROM #tmp WHERE id = t.id)\n\nDROP TABLE tablename\n</pre>\n'}, {'answer_id': 76901, 'author': 'Dave Lievense', 'author_id': 13679, 'author_profile': 'https://Stackoverflow.com/users/13679', 'pm_score': 1, 'selected': False, 'text': "<p>Without knowing the exact structure of your tables or what type of database you're using it's hard to answer.  However if you're using MS SQL and if you have a true date/time field that has different times that the records were entered on the same date then something like this should work:</p>\n\n<pre><code>select record_id, \n       convert(varchar, date_created, 101) as log date, \n       count(distinct date_created) as num_of_entries\nfrom record_log_table\ngroup by convert(varchar, date_created, 101), record_id\nhaving count(distinct date_created) &gt; 1\n</code></pre>\n\n<p>Hope this helps.</p>\n"}, {'answer_id': 77304, 'author': 'Bob Probst', 'author_id': 12424, 'author_profile': 'https://Stackoverflow.com/users/12424', 'pm_score': 2, 'selected': False, 'text': "<p>The top post (Leigh Caldwell) will not return duplicate records and needs to be down modded.  It will identify the duplicate keys.  Furthermore, it  will not work if your database doesn't allows the group by to not include all select fields (many do not).</p>\n\n<p>If your date field includes a time stamp then you'll need to truncate that out using one of the methods documented above ( I prefer: dateadd(dd,0, datediff(dd,0,@DateTime)) ).</p>\n\n<p>I think Scott Nichols gave the correct answer and here's a script to prove it:</p>\n\n<pre><code>declare @duplicates table (\nid int,\ndatestamp datetime,\nipsum varchar(200))\n\ninsert into @duplicates (id,datestamp,ipsum) values (1,'9/12/2008','ipsum primis in faucibus')\ninsert into @duplicates (id,datestamp,ipsum) values (1,'9/12/2008','Vivamus consectetuer. ')\ninsert into @duplicates (id,datestamp,ipsum) values (2,'9/12/2008','condimentum posuere, quam.')\ninsert into @duplicates (id,datestamp,ipsum) values (2,'9/13/2008','Donec eu sapien vel dui')\ninsert into @duplicates (id,datestamp,ipsum) values (3,'9/12/2008','In velit nulla, faucibus sed')\n\nselect a.* from @duplicates a\ninner join (select id,datestamp, count(1) as number\n              from @duplicates\n          group by id,datestamp\n            having count(1) &gt; 1) b\n       on (a.id = b.id and a.datestamp = b.datestamp)\n</code></pre>\n"}, {'answer_id': 98368, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': '<pre><code>SELECT RecordID\nFROM aTable\nWHERE SameDate IN\n    (SELECT SameDate\n    FROM aTable\n    GROUP BY SameDate\n    HAVING COUNT(SameDate) &gt; 1)\n</code></pre>\n'}, {'answer_id': 98536, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p>TrickyNixon writes;</p>\n\n<blockquote>\n  <p>The top post (Leigh Caldwell) will not return duplicate records and needs to be down modded.</p>\n</blockquote>\n\n<p>Yet the question doesn\'t ask about duplicate records.  It asks about duplicate record-ids on the same date...</p>\n\n<p>GROUP-BY,HAVING seems good to me.  I\'ve used it in production before.</p>\n\n<p>.</p>\n\n<p>Something to watch out for:</p>\n\n<p>SELECT ... FROM ... GROUP BY ... HAVING count(*)>1</p>\n\n<p>Will, on most database systems, run in O(NlogN) time.  It\'s a good solution.  (Select is O(N), sort is O(NlogN), group by is O(N), having is O(N) -- Worse case.  Best case, date is indexed and the sort operation is more efficient.)</p>\n\n<p>.</p>\n\n<p>Select ... from ..., ....  where a.data = b.date</p>\n\n<p>Granted only idiots do a Cartesian join.  But you\'re looking at O(N^2) time.  For some databases, this also creates a "temporary" table.  It\'s all insignificant when your table has only 10 rows.  But it\'s gonna hurt when that table grows!</p>\n\n<p>Ob link:  <a href="http://en.wikipedia.org/wiki/Join_(SQL)" rel="nofollow noreferrer">http://en.wikipedia.org/wiki/Join_(SQL)</a></p>\n'}]
['https://Stackoverflow.com/questions/76724', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']