Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jprumos
Starting Member
3 Posts |
 Posted - 2009-11-10 : 17:57:15
|
| I am trying to perform a select on a database of file information. I am looking for Duplicate files and am using FileName, FileSize and FileHash to test for duplicates. I want to return all rows where these three columns are identical, however I want to return all the columns of the specific rows, not just the ones that match. Here is the DDL for the table.CREATE TABLE MediaInfo.dbo.FileInfo ( FileNumber int NOT NULL, MediaNumber int NOT NULL, FileCount int, FilePath varchar(256), FileName varchar(256), FileSize bigint, FileDate datetime, FileHash varchar(16), PRIMARY KEY (FileNumber)); |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-11-10 : 18:24:01
|
will this work?select COUNT(*), FileName, FileSize and FileHash FROM MediaInfo.dbo.FileInfo GROUP BY FileName, FileSize and FileHash <><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
jprumos
Starting Member
3 Posts |
Posted - 2009-11-10 : 18:29:01
|
| I want to return the FileNumber, MediaNumber, FilePath and FileDate for each row as well. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-11-11 : 10:34:17
|
| you want filenumber too? is that a unique ID ? unless you want that concatenated somehow I don't think you can do that, if filenumber is unique<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
|
|
|
|
|
Finding Duplicate rows on multiple columns.