Order Groups - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Order Groups

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-01-05 : 13:17:47

Hi,

Let this be a set of rows created by a group operation but before the aggregate has been applied...

Order, Allow
============
1 null
2 null
3 0
4 1
5 0

Now, starting with the lowest order, I want to return the first non-null value of allow.

So given the above table, my final group row after the aggregate function has been applied should return a value of '0' for 'allow'.

Conversely, 'Max(Allow)' will give '1' which is wrong. The row with order 3 should override this value with a '0'.

Is it possible to have an aggregate function which does this?

Cheers, XF.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 13:33:33

If I understand you correctly, you really don't want min or max allow. You want allow for the min and max order, correct?


use northwind
set nocount on
go
create table junk
	([order] int identity(1,1) primary key
	,allow bit null)
go

insert junk (allow)
select null union all
select null union all
select 0 union all
select 1 union all
select 0

select	a.allow [minAllow]
from	junk a
join	(
	select min([order]) [order]
	from	junk
	where	allow is not null
	) b
		on b.[order] = a.[order]

select	a.allow [maxAllow]
from	junk a
join	(
	select max([order]) [order]
	from	junk
	where	allow is not null
	) b
		on b.[order] = a.[order]

go
drop table junk

Be One with the Optimizer
TG

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-01-05 : 16:15:52

I want allow for the min order where allow is not null.

However, I want this for all the different groups created by grouping a relation. So in your example, junk would be just one group and as far as I know its not possible to apply relational operators to a group. You can only use aggregate functions on columns.

So if your junk table has an extra column so that it becomes...

create table junk
	(groupID int ,allow bit null, order int)

...and there's a different order for each group. Then I don't think its possible to get allow for the min order of each group from the following...


select	groupID, allow ??
from	junk
group by groupID

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 16:29:59

That may be true, I was just working off your initial table structure and sample data. Post the actual structure with some sample data along with your desired results and we'll see.

Be One with the Optimizer
TG

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-01-05 : 16:51:36

Here's a table of permissions...

CREATE TABLE [dbo].[tbl_Folders_Groups_Permissions] (
	[FolderID] [int] NOT NULL ,
	[GroupID] [int] NOT NULL ,
	[FolderPermissionID] [int] NOT NULL ,
	[Allow] [bit] NOT NULL 
) ON [PRIMARY]
GO

Here's a sp which given a folder and a group iterates up the hierarchy to get the set of all folders in the path.

ALTER      PROCEDURE sp_GetFolderPermissionsForGroup

	@folderID int,
	@groupID int

AS

-- declare variables
DECLARE @resultSet table ( folderPermissionID int, ordering int )
DECLARE @parentID int

DECLARE @ACounterVariable int
SET @ACounterVariable = 1

--loop until a top level node has been reached or the loop is apparently stuck
WHILE (@ACounterVariable < 10000)

	BEGIN
		-- accumulate results in table variable
		INSERT INTO @resultSet ( folderID, ordering ) VALUES ( @folderID, @ACounterVariable )

		--get data for next level
		SELECT @parentID = ParentID FROM tbl_Folders WHERE FolderID = @folderID

		--check to see if a top of node has been reached
		IF @parentID IS NULL BREAK

		--update
		SET @folderID = @parentID

		-- increment counter
		SET @ACounterVariable = @ACounterVariable + 1
	END

Then I was hoping to join the path to the permissions table...

SELECT
	FolderPermissionID,
	Allow ??
FROM
	@resultSet r
JOIN
	tbl_Folders_Groups_Permissions f
ON
	f.FolderID = r.folderID
	AND
	f.GroupID = @groupID
GROUP BY
	FolderPermissionID

If this isn't possible then I'm planning to get all the permissions for each folder one by one upon each iteration up the path and accumulate the permissions in a table variable. I just think this will be alot more work for the db to do.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 17:20:38

Sorry X, I'm being thick. Could you provide a couple sample rows and the expected results?
Something like this:


insert [tbl_Folders_Groups_Permissions]
select 1,1,1,1 union all
select 2,1,2,null
select...

--Contents of @resultSet after the looping
insert @resultSet 
select ...


expected Results:
FolderPermissionID	Allow
------------------    -------
                 1          1
                 2          0

Be One with the Optimizer
TG

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-01-05 : 18:13:58

Well I've got it working with what I think is a slower method but hopefully it should illustrate what I'm after...

use tempdb

CREATE TABLE tbl_FolderPermissions (
	[FolderPermissionID] [int] NOT NULL ,
	[PermissionName] [varchar] (50)  NOT NULL
) ON [PRIMARY]


CREATE TABLE tbl_Folders (
	[FolderID] [int] NOT NULL ,
	[ParentID] [int] NULL ,
	[FolderName] [varchar] (100) NOT NULL ,
	[CascadePermissions] [bit] NOT NULL 
) ON [PRIMARY]


CREATE TABLE tbl_Folders_Groups_Permissions (
	[FolderID] [int] NOT NULL ,
	[GroupID] [int] NOT NULL ,
	[FolderPermissionID] [int] NOT NULL ,
	[Allow] [bit] NOT NULL 
) ON [PRIMARY]

INSERT tbl_FolderPermissions
SELECT 1, 'Upload file' UNION ALL
SELECT 2, 'Delete file' UNION ALL
SELECT 3, 'Create folder'

INSERT tbl_Folders
SELECT 1, null, 'Parent Folder', 1 UNION ALL
SELECT 2, 1, 'Child Folder', 1

INSERT tbl_Folders_Groups_Permissions
SELECT 2, 1, 1, 0 UNION ALL
SELECT 1, 1, 1, 1 UNION ALL
SELECT 1, 1, 2, 1
GO

CREATE PROCEDURE sp_GetFolderPermissionsForGroup

	@folderID int,
	@groupID int

AS

-- declare variables
DECLARE @resultSet table ( folderPermissionID int, allow bit )
INSERT INTO @resultSet SELECT folderPermissionID, null FROM tbl_FolderPermissions

DECLARE @parentID int
DECLARE @allowCascade bit

DECLARE @ACounterVariable int
SET @ACounterVariable = 1

--loop until a top level node has been reached or the loop is apparently stuck
WHILE (@ACounterVariable < 10000)

	BEGIN
		UPDATE @resultSet
		SET
			allow = CASE WHEN r.allow IS NULL AND p.allow IS NOT NULL THEN p.allow ELSE r.allow END
		FROM
			@resultSet r, tbl_Folders_Groups_Permissions p
		WHERE
			r.folderPermissionID = p.FolderPermissionID
			AND
			p.FolderID = @folderID
			AND
			p.GroupID = @groupID
			

		--get data for next level
		SELECT @parentID = f1.ParentID, @allowCascade = f2.CascadePermissions
		FROM tbl_Folders f1
		LEFT JOIN tbl_Folders f2
		ON f1.ParentID = f2.FolderID
		WHERE f1.FolderID = @folderID

		--check to see if a top of node has been reached
		IF @parentID IS NULL OR @allowCascade = 0 BREAK

		--update
		SET @folderID = @parentID

		-- increment counter
		SET @ACounterVariable = @ACounterVariable + 1
	END

SELECT r.*, f.PermissionName FROM @resultSet r JOIN tbl_FolderPermissions f on r.FolderPermissionID = f.FolderPermissionID
GO

EXECUTE sp_GetFolderPermissionsForGroup 2, 1

drop PROCEDURE sp_GetFolderPermissionsForGroup
drop table tbl_FolderPermissions
drop table tbl_Folders
drop table tbl_Folders_Groups_Permissions

So you can see that folder 2 inherits folder 1's allow value for permission 2 but has its own allow value for permission 1. Permission 3 defaults to a no as a result of it being null.

The original question is about having the sp only get the folder's in the path when it iterates up the hierarchy and then using this set resolve the correct allow value in one query.

I hope this is making a bit more sense!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources