GROUP BY, take Foo from row where Bar=Min(Bar)

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 2005 Forums

Transact-SQL (2005)

GROUP BY, take Foo from row where Bar=Min(Bar)

Author

Topic

Jenda
Starting Member

29 Posts

Posted - 2007-10-21 : 08:39:03

Let's assume I have a table GroupAncestors (AncestorId int, GroupId int, Generation tinyint) that defines the structure of groups of users (for each group there is row=(GroupId,GroupId,0), if it's a subgroup there is row=(ParentId,GroupId,1) etc.).
Then I have a table that specifies that some Sites (whatever that is) are free for a group GroupFreeSites(GroupId int,SiteId int,Isfree bit). What I'd like is to be able to inherit the free sites from parent groups to subgroups AND be able to overwrite it on a subgroup level.

select GA.GroupId, GFS.SiteId, GFS.IsFree, GA.Generation
from dbo.GroupFreeSites as GFS
JOIN dbo.GroupAncestors as GA with (nolock) ON GA.AncestorId = GFS.GroupId

will give me all settings for all groups, their own or inherited. But if a site is marked free on the parent level and then not free for a subgroup I get eg.

GroupId, SiteId, IsFree, Generation
124, 1047, 1, 1
124, 1047, 0, 0

What I would like is to get the value of IsFree with the minimal Generation for each combination of GroupId and SiteId. Something like

select GA.GroupId, GFS.SiteId,
(THE GFS.IsFree FOR WHICH GA.Generation = MIN(GA.Generation)) as IsFree
from dbo.GroupFreeSites as GFS
JOIN dbo.GroupAncestors as GA with (nolock) ON GA.AncestorId = GFS.GroupId
GROUP BY GA.GroupId, GFS.SiteId

Is there any clever trick to do this? I'd really like to do this as a view so that I could then easily select all sites that are free for a group, check whether there are any free sites for a group etc.

The numbers of groups and free sites are small.

Thanks, Jenda

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-21 : 10:48:13

Select GA.GroupID,GFS.SiteID

From GroupFreeSites GFS INNER JOIN dbo.GroupAncestors GA
on GA.AncestorID = GFS.GroupID
inner join
(Select Min(Generation) as MinGen,AncestorID FROM GroupAncestors Group by AncestorID) as MG
on GA.AncestorID = MG.AncestorID
Where MG.MinGen = GFS.IsFree
Group by GA.GroupID,GFS.SiteID

You join on the Subquery which pulls the Min(Generation) as MinGen From the GA table and aliased as MG above. You then filter the results for WHERE the IsFree = MinGen. Untested.

Jenda
Starting Member

29 Posts

Posted - 2007-10-21 : 11:36:22

I think you meant something like

select GA.GroupId, GFS.SiteId, GFS.IsFree, GA.Generation, GA.AncestorId
from dbo.GroupFreeSites as GFS
JOIN dbo.GroupAncestors as GA with (nolock) ON GA.AncestorId = GFS.GroupId
JOIN (
select GA.GroupId, GFS.SiteId, MIN(GA.Generation) as MinGen
from dbo.GroupFreeSites as GFS
JOIN dbo.GroupAncestors as GA with (nolock) ON GA.AncestorId = GFS.GroupId
GROUP BY GA.GroupId, GFS.SiteId
) as MG ON MG.GroupId = GA.GroupId and MG.Siteid = GFS.SiteId and GA.Generation = MinGen

because I'm interested in the minimal Generation for which there is a row in GroupFreeSites. This works and probably it's what I'll end up using ... unless someone can think of something better :-)

Jenda

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources