September 7, 2011 at 7:56 am
I'm trying to find duplicate records, so basically I wanted to do a
select distinct * from myTable having count(*) > 1
But once I add the Having it wants a Group By Clause, and Group By * doesn't work, and I really didn't want to add all 50 rows to the statement.
Thanks.
September 7, 2011 at 8:11 am
Hmm, the table doesn't have an unique id and or primary key? Or else just right click on the table, select top 1000 rows, delete the top 1000, copy all the fields, paste them in the group by clause. This should get your result.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
September 7, 2011 at 8:12 am
You would need to group by all your columns. You can't do an aggregate function if you don't specify what to group by. If the reason you don't want all the columns is because you don't want to type them just drag the columns from the table definition in the object explorer. It is only a couple seconds to add all the columns.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 7, 2011 at 9:08 am
Dynamic SQL:
DECLARE @tableWithDuplicates sysname
SET @tableWithDuplicates = 'SomeTableName'
DECLARE @sql nvarchar(max)
SET @sql = STUFF((
SELECT ',' + name
FROM sys.columns
WHERE object_id = OBJECT_ID(@tableWithDuplicates)
ORDER BY column_id
FOR XML PATH('')
),1,1,'')
SET @sql = ' SELECT ' + @sql +
' FROM ' + QUOTENAME(@tableWithDuplicates) +
' GROUP BY ' + @sql +
' HAVING COUNT(*) > 1 '
EXEC(@sql)
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply