Delete duplicate records - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Delete duplicate records

Author

Topic

kopzen
Starting Member

34 Posts

Posted - 2008-06-24 : 04:42:31

Hi,
Please help me in constructing a query that will delete duplicate records in a table;
Please see my table structure below:

CREATE TABLE LATEST_DATA (
[ID] int NOT NULL,
[IPage] varchar(100) NULL,
[IDevice] varchar(255) NULL,
[IGroup] varchar(255) NULL,
[IField] varchar(255) NULL,
[IValue] varchar(255) NULL,
[IIcon] int NULL,
[IID] int NULL,
[ReportID] int NOT NULL,
[RVersion] varchar(255) NULL,
[RHost] varchar(255) NULL,
[RUser] varchar(255) NULL,
[RLocation] varchar(255) NULL,
[RDateTime] varchar(16) NULL,
[RComplete] bit NOT NULL
);

-- Insert Latest data
INSERT
INTO LATEST_DATA
SELECT
[Item].[ID],
[Item].[IPage],
[Item].[IDevice],
[Item].[IGroup],
[Item].[IField],
[Item].[IValue],
[Item].[IIcon],
[Item].[IID],
[Item].[ReportID] ,
[Report].[RVersion],
[Report].[RHost],
[Report].[RUser],
[Report].[RLocation],
[Report].[RDateTime],
[Report].[RComplete]
FROM
[dbo].[Item],
[dbo].[Report]
WHERE
[Report].[ID] = [ITEM].[ReportID] AND
[Report].[ID] = ( SELECT
MAX([Report].[ID])
FROM
[dbo].[Report]
);

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 04:44:02

Are you using SQL 2000 or 2005? ALso can you post some sample data to illustrate on which all fields you decide on duplicates.

kopzen
Starting Member

34 Posts

Posted - 2008-06-24 : 05:25:14

Thanks for the quick reply

SQL 2005

Fields that get populated with duplicates are IPage, Rhost and Idevice.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 05:32:27

quote:
Originally posted by kopzen

Thanks for the quick reply

SQL 2005

Fields that get populated with duplicates are IPage, Rhost and Idevice.

DELETE t 
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY IPage, Rhost,Idevice ORDER BY ID) AS RowNo,*
FROM LATEST_DATA)t
WHERE t.RowNo>1

kopzen
Starting Member

34 Posts

Posted - 2008-06-24 : 06:48:54

Thanks for that but it also deletes other useful records

I have 5 columns, IPage, Rhost, IDevice, IField and IValue;
And 6 Rows
IPage displays Logical Drives from 1-6 (Should only see one Logical Drive)
Rhost displays PC Name from 1-6 (Should only see one PC Name)
Idevice displays C: from 1-6 (Should only see one C:)
Ifield displays diff Hard drive parameters (should not be touched)
IValue displays more diff parameters ( should also stay the same)

IPage
Local Drive
Local Drive
Local Drive
Local Drive
Local Drive
Local Drive
Local Drive

Rhost
PC Name
PC Name
PC Name
PC Name
PC Nale
PC Name

IDevice
C:
C:
C:
C:
C:
C:

IField
%Free
Drive Type
File System
Free Space
Total SIze
Volume Serial

IValue
43%
Local Disk
NTFS
32588 MB
76316 MB
848C-C1CF

I am creating a report using Microsoft visual Studio, The output should be something like:

Logical Drives - PC Name

Drive: C
Drive Type: Local Disk
File System: NTFS
Volume Serial: 848C-C1CF
Total Size: 76316 MB
Free Space: 32588 MB
%Free: 43%

Below is a stored procedure I created:

ALTER procedure [dbo].[aidapr]
as
SELECT
Ipage,
Rhost,
Idevice,
Ifield,
Ivalue
from dbo.latest_data where IDevice ='c:';

Thanks is advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 07:54:51

Which all records need to be deleted in your sample data?

kopzen
Starting Member

34 Posts

Posted - 2008-06-24 : 08:11:42

-I only want to see one Local Disk entry from IPage
- One PC Name from Rhost
- One C: from Idevice and all values from Ifield and Ivalue

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 08:16:56

quote:
Originally posted by kopzen

-I only want to see one Local Disk entry from IPage
- One PC Name from Rhost
- One C: from Idevice and all values from Ifield and Ivalue

I think this is more of a formatting issue. you need to do this at your front end application.Similar to hide duplicates in SQL reporting services.

kopzen
Starting Member

34 Posts

Posted - 2008-06-24 : 08:29:37

Thanks a mil, I found the hide duplicates option inside visual studio

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources