T-SQL remove duplicates from table?

Question

We have a table that lists png images and their source URL's.

Sometimes the table has rows with the same image URL's but different image pixel widths and heights. I want to remove such duplicates, keeping only the duplicate that has the biggest image width then biggest image height.

I've tried various methods that I used to use in MSAccess (like GroupBy and First but First for example isn't available in SQL Server so thought I'd ask for T-SQL help).

Can anyone give T-SQL that would remove the duplicates (keeping the biggest image row of each duplicate)?

CREATE TABLE [dbo].[tblImageSuggestions]
(
    [CounterID] [bigint] IDENTITY(700996,1) NOT NULL,
    [CreatedDateTime] [datetime] NOT NULL,
    [EmailAddress] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ImageOriginalURL] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ImageOriginalWidthPixels] [int] NOT NULL,
    [ImageOriginalHeightPixels] [int] NOT NULL,

    CONSTRAINT [PK_tblImageSuggestions] 
        PRIMARY KEY CLUSTERED ([CounterID] ASC)
                WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
                     IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
                     ALLOW_PAGE_LOCKS  = ON)
)

SET IDENTITY_INSERT [dbo].[tblImageSuggestions] ON

INSERT [dbo].[tblImageSuggestions] ([CounterID], [CreatedDateTime], [EmailAddress], [ImageOriginalURL], [ImageOriginalWidthPixels], [ImageOriginalHeightPixels]) 
VALUES (701030, CAST(0x0000A6AD0005543F AS DateTime), N'[email protected]', N'MyURL1', 1024, 1024)

INSERT [dbo].[tblImageSuggestions] ([CounterID], [CreatedDateTime], [EmailAddress], [ImageOriginalURL], [ImageOriginalWidthPixels], [ImageOriginalHeightPixels]) 
VALUES (701031, CAST(0x0000A6AD00055445 AS DateTime), N'[email protected]', N'MyURL2', 450, 450)

INSERT [dbo].[tblImageSuggestions] ([CounterID], [CreatedDateTime], [EmailAddress], [ImageOriginalURL], [ImageOriginalWidthPixels], [ImageOriginalHeightPixels]) 
VALUES (701032, CAST(0x0000A6AD00055489 AS DateTime), N'[email protected]', N'MyURL3', 3000, 3000)

INSERT [dbo].[tblImageSuggestions] ([CounterID], [CreatedDateTime], [EmailAddress], [ImageOriginalURL], [ImageOriginalWidthPixels], [ImageOriginalHeightPixels]) 
VALUES (701033, CAST(0x0000A6AD00055768 AS DateTime), N'[email protected]', N'MyURL2', 1024, 1024)

INSERT [dbo].[tblImageSuggestions] ([CounterID], [CreatedDateTime], [EmailAddress], [ImageOriginalURL], [ImageOriginalWidthPixels], [ImageOriginalHeightPixels]) 
VALUES (701034, CAST(0x0000A6AD00055771 AS DateTime), N'[email protected]', N'MyURL1', 450, 450)

INSERT [dbo].[tblImageSuggestions] ([CounterID], [CreatedDateTime], [EmailAddress], [ImageOriginalURL], [ImageOriginalWidthPixels], [ImageOriginalHeightPixels]) 
VALUES (701035, CAST(0x0000A6AD0005577A AS DateTime), N'[email protected]', N'MyURL4', 768, 768)

SET IDENTITY_INSERT [dbo].[tblImageSuggestions] OFF

Can you show us what you have thus far as far as non-working code? We need to see your attempted approach in order to tell you where you might need some help. Giving simple table definitions and asking for a solution isn't really in line with how to ask a question on SO. — user2366842
– user2366842, Commented Oct 28, 2016 at 1:27
if you have some linked data or table to that table you better investigate it first. that may worsen your problem. — Beginner
– Beginner, Commented Oct 28, 2016 at 1:47

John Cappelletti · Accepted Answer · 2016-10-28 01:38:48Z

3

;with cte as (
    Select *,RowNr=Row_Number() over (Partition By ImageOriginalURL Order by ImageOriginalWidthPixels*ImageOriginalHeightPixels Desc)
     From  [tblImageSuggestions]
)
--Delete From cte Where RowNr>1    
Select * from cte Where RowNr>1  -- To be deleted ... Remove if Satisfied

answered Oct 28, 2016 at 1:38

John Cappelletti

82.2k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Cappelletti Over a year ago

@user1946932 Happy to help

Collectives™ on Stack Overflow

T-SQL remove duplicates from table?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related