Help with aggregate query - 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

Help with aggregate query

Author

Topic

SQLGeno
Starting Member

13 Posts

Posted - 2011-02-27 : 19:57:19

Hi guys,
Great forum with lots of people smarter than I. I am a bit confused with a query I have.

This is my data.
Id User Date
4771 macebr 2010-05-04 15:01:35.143
5255 ilslea 2010-04-07 13:43:06.870
5385 breenv 2010-01-27 17:13:58.097
5385 breenv 2010-01-27 17:13:48.860
5617 dowdg 2010-01-11 12:23:52.493
5617 dowdg 2010-01-11 12:23:48.273
5619 makung 2010-01-11 11:19:22.967
5619 makung 2010-01-11 11:03:51.227

Now I would like to the Id, User and Date for the earliest Row for a given Id. i.e - From the above I want:
Id User Date
4771 macebr 2010-05-04 15:01:35.143
5255 ilslea 2010-04-07 13:43:06.870
5385 breenv 2010-01-27 17:13:48.860
5617 dowdg 2010-01-11 12:23:52.493
5617 dowdg 2010-01-11 12:23:48.273
5619 makung 2010-01-11 11:19:22.967

This almost gets it but as soon as I add the Date column I get the extra rows back
SELECT MIN(Date)
,User
--,Date FROM @GrantApps
GROUP BY Id
--,User
ORDER BY Id

With assistance I got it returning the right data using;
SELECT User, Id, Date
FROM @GrantApps gr
WHERE Date = (
SELECT MIN(Date)
FROM @GrantApps
WHERE Id = gr.Id
GROUP BY Id)
ORDER BY Id

However, I suspect there is an easier\better way to achieve this using a variation of my first attempt (i.e some sort of aggregate query)? If so, I would appreciate enlightenment.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-28 : 00:08:24

Check if this is what you are looking for ..

Select User,ID,Min(Date)
From @GrantApps
group by User,ID
Order by ID

Cheers
MIK

kazi
Starting Member

8 Posts

Posted - 2011-02-28 : 02:47:04

your requirement is not clear.
the sample output you submit is not match with the query you make.

Kazi

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-28 : 05:35:36

With SQL2005 and above use the ROW_N@UMBER windowed function:


-- *** Test Data ***
CREATE TABLE #t
(
	Id int NOT NULL
	,[User] varchar(50) NOT NULL
	,[Date] datetime NOT NULL
)
INSERT INTO #t
SELECT 4771, 'macebr', '2010-05-04 15:01:35.143'
UNION ALL SELECT 5255, 'ilslea', '2010-04-07 13:43:06.870'
UNION ALL SELECT 5385, 'breenv', '2010-01-27 17:13:58.097'
UNION ALL SELECT 5385, 'breenv', '2010-01-27 17:13:48.860'
UNION ALL SELECT 5617, 'dowdg', '2010-01-11 12:23:52.493'
UNION ALL SELECT 5617, 'dowdg', '2010-01-11 12:23:48.273'
UNION ALL SELECT 5619, 'makung', '2010-01-11 11:19:22.967'
UNION ALL SELECT 5619, 'makung', '2010-01-11 11:03:51.227'
-- *** End Test Data ***

;WITH tRN
AS
(
	SELECT Id, [User], [Date]
		,ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY [Date]) AS RN
	FROM #t
)
SELECT Id, [User], [Date]
FROM tRN
WHERE RN = 1

kazi
Starting Member

8 Posts

Posted - 2011-02-28 : 06:07:57

You may find solution as below

Select id, user, date
from
@GrantApps
Where
Rank() Over (Partition By id Order By date)=1

Kazi

SQLGeno
Starting Member

13 Posts

Posted - 2011-02-28 : 19:32:30

MIK_2008
That is what I had already tried. I get multiple orws for an ID using that query. Only want one row per ID, and that row is the one with the lowest Date for that Id.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources