need help with duplicates and group by

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

need help with duplicates and group by

Author

Topic

theapostle
Starting Member

2 Posts

Posted - 2010-01-23 : 22:19:24

I'm a complete noob. I have the following scenario. I need to be able return only the non-duplicate urls created by each user (name) grouped by url count within the last 24 hours (showing the first occurrence datetime of the url). I hope that makes sense. Appreciate any insight. Thanks.

id url name date
7fe5f6c5-8874-4911-95de-2ba489ecfc5a http://www.google.com/ joe 1/23/2010 10:56:31 AM
40dc0abf-be05-47a3-92d1-4f8332ba98af http://www.google.com/ joe 1/23/2010 11:43:42 PM
1664e2d4-8efa-42b4-b605-76338ec2cca4 http://www.google.com/ joe 1/23/2010 10:56:30 AM
d59ec0d3-fbe2-4a60-a700-6a0426566825 http://www.msn.com/ sally 1/23/2010 10:35:19 AM
ccd6634b-dbd4-4adc-a4f1-db764ecec2b7 http://www.msn.com/ frank 1/23/2010 12:49:11 AM
c8aa3d15-d448-4782-8563-022026f0012b http://www.bing.com/ mary 1/23/2010 1:50:26 AM
5475a767-e747-463a-b4ff-5ee312d40ca8 http://www.google.com/ ted 1/23/2010 9:16:07 PM
d160fed0-424d-4e74-a1a2-eec1de5a5dec http://www.msn.com/ eddie 1/23/2010 5:01:22 PM

Based on this data (and filtering within the last 24 hours) I would try to return:
url count first_occurrence_date
http://www.google.com/ 2 1/23/2010 10:56:30 AM
http://www.msn.com/ 3 1/23/2010 10:35:19 AM
http://www.bing.com/ 1 1/23/2010 1:50:26 AM

USE [tester];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[test] (
[id] uniqueidentifier ROWGUIDCOL NULL DEFAULT (newid()),
[url] nvarchar(255) NOT NULL,
[name] nchar(25) NOT NULL,
[date] datetime2(7) NULL)
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE);
GO

insert into [dbo].[test]([id],[url],[name],[date]) values ('7fe5f6c5-8874-4911-95de-2ba489ecfc5a',N'http://www.google.com/',N'joe ','2010-01-23 10:56:31')
insert into [dbo].[test]([id],[url],[name],[date]) values ('40dc0abf-be05-47a3-92d1-4f8332ba98af',N'http://www.google.com/',N'joe ','2010-01-23 23:43:42')
insert into [dbo].[test]([id],[url],[name],[date]) values ('1664e2d4-8efa-42b4-b605-76338ec2cca4',N'http://www.google.com/',N'joe ','2010-01-23 10:56:30')
insert into [dbo].[test]([id],[url],[name],[date]) values ('d59ec0d3-fbe2-4a60-a700-6a0426566825',N'http://www.msn.com/',N'sally ','2010-01-23 10:35:19')
insert into [dbo].[test]([id],[url],[name],[date]) values ('ccd6634b-dbd4-4adc-a4f1-db764ecec2b7',N'http://www.msn.com/',N'frank ','2010-01-23 00:49:11')
insert into [dbo].[test]([id],[url],[name],[date]) values ('c8aa3d15-d448-4782-8563-022026f0012b',N'http://www.bing.com/',N'mary ','2010-01-23 01:50:26')
insert into [dbo].[test]([id],[url],[name],[date]) values ('5475a767-e747-463a-b4ff-5ee312d40ca8',N'http://www.google.com/',N'ted ','2010-01-23 21:16:07')
insert into [dbo].[test]([id],[url],[name],[date]) values ('d160fed0-424d-4e74-a1a2-eec1de5a5dec',N'http://www.msn.com/',N'eddie ','2010-01-23 17:01:22')

theapostle
Starting Member

2 Posts

Posted - 2010-01-24 : 00:51:03

This did the trick:

select url, [count] = count(distinct([Name])),
[first_occurrence_date] = min([date]) from dbo.test group by [url]

Hope it helps someone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources