my understanding of the original 3 column table was different... but in the case above why not SELECT * FROM @table WHERE Author = @author AND Date = @date? my wonder is why do you want to see 2 exactly the same rows shown twice (instead of - at maximum - been notified of number of occurrences of the same row?)
What if you don't know what date it could happen? You just want any date where two things happened together. What if you think that anytime two stories by (any) two different people comes out on a given day, there is collusion, or there is some correlation, or .... The point is you want to see duplicates, then see if there is causation in the duplicate, or maybe you want to do some operation on the duplicates, whatever...
well i clearly do not understand your problem. if there are 2 different people then you do not have identical rows and so on... i guess it is important that you solved your problem (whatever it was). just keep in mind that designing a proper database (indexing, data types, keys, relationship, triggers) adds a lot of speed and saves you a lot of coding and errors.
ok now i see it. so it is not about rows but rather that you want to see all news articles if and only if a specific author posted more than 1 article in a given time period...from the original post that was not obvious. but then the solution could be much easier then the one above. e.g. select * from TBL where author in (select author from TBL where date=@date group by author having count(author)>1) cheers
Not quite. I want to see _any_ two entries where they matched on date (without the time part), but I want to select whatever (rest of the) columns I want when displaying the table. In fact, I don't want entries by the _same_ person at all. I want to exclude those (Say an author was only allowed to enter one news story per day, but it could be any day). I want to see who else wrote on that day, and I want to see all the columns for the return table.
Nitro.. group by the key that shows duplicates, counting the number of entries that are the same. put the results of the select INTO a second temporary table and select on the count say you have people with duplicate names.. SELECT name, count(*) 'countx' INTO #mylist FROM listofnames GROUP BY name --show all entries with duplicates SELECT name FROM #mylist WHERE countx > 1 --show all entries without duplicates SELECT name FROM #mylist WHERE countx = 1 --throw away the temporary table DROP TABLE #mylist
patoo, Thank you for your code. But here is the problem I am trying to solve: I have a table with columns x,y,z. x can contain many duplicates (say the date without the time part). I want a list of all the x duplicate rows, and their corresponding y and z columns in a table. Your code only allows me to see the column that is duplicated I believe, something I could achieve as posted in the original code in this thread.
If I understand your question correctly, this is a common problem. You just have to join the query selecting the duplicates with another query that's selecting the columns you want from the same table e.g. <pre> SELECT x, y, z FROM table INNER JOIN (SELECT x FROM table GROUP BY x HAVING count(*) > 1) AS duplicates ON table.primary_key = duplicates.primary_key </pre> ...or something like that. There are a couple of possibilities depending on which database you are using (I missed if you mentioned it).