Question on aggregates 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

SQL Server 2000 Forums

SQL Server Development (2000)

Question on aggregates and group by

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-07-05 : 17:17:31

I am wondering why I am getting different results using the following two queries. There is obviously something I haven't understood using the group by clause in this context, so if any kind soul would be willing to offer guidance I would appreciate it.

create table #deleteme
(
Date smalldatetime,
id varchar(3),
Val integer
)
go

insert into #deleteme
(date, id, val)
values
('2000-01-01', 'ABC', 3)
insert into #deleteme
(date, id, val)
values
('2000-01-02', 'ABC', 4)
insert into #deleteme
(date, id, val)
values
('2000-01-03', 'ABC', 5)
go

select id, min(date) from #deleteme group by id --query#1 gives me one thing
go

select id, min(date), Val from #deleteme group by id, Val --query#2 gives me another, although I want query#1 plus the corresponding value
go

drop table #deleteme
go

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-05 : 17:34:21

I take it that it's not the min/max of Val your after but the corresponding Val to the min of date (even though your example is the min)

Is it something like this your after


SELECT a.[id], a.[date], a.Val 
FROM #deleteme a INNER JOIN (SELECT [id], MIN([date]) AS [date] FROM #deleteme GROUP BY [id]) b 
			ON a.[id] = b.[id] AND a.[date] = b.[date]

Andy

Beauty is in the eyes of the beerholder

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-07-05 : 17:36:46

Indeed it is. Cheers, mate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources