paging criteria - 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)

paging criteria

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2011-02-07 : 05:33:38

Any help is greatly appreciated. Thanks in advance..

I have this data...


select 1 as empid, 11 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 1 as empid, 12 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 1 as empid, 13 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 21 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 22 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 23 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 3 as empid, 31 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 3 as empid, 32 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 3 as empid, 33 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 4 as empid, 41 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 4 as empid, 42 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 4 as empid, 43 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid union all
select 5 as empid, 52 as staffid, '2' as companyid, '1' as region, '2a' as branchid union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid

Expected result...



select 1 as empid, 11 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 1 as empid, 12 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 1 as empid, 13 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 2 as empid, 21 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 2 as empid, 22 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 2 as empid, 23 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 3 as empid, 31 as staffid, '1' as companyid, '2' as region, '1a' as branchid, 2 as page union all
select 3 as empid, 32 as staffid, '1' as companyid, '2' as region, '1a' as branchid, 2 as page union all
select 3 as empid, 33 as staffid, '1' as companyid, '2' as region, '1a' as branchid, 2 as page union all
select 4 as empid, 41 as staffid, '2' as companyid, '1' as region, '1a' as branchid, 3 as page union all
select 4 as empid, 42 as staffid, '2' as companyid, '1' as region, '1a' as branchid, 3 as page union all
select 4 as empid, 43 as staffid, '2' as companyid, '1' as region, '1a' as branchid, 3 as page union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid, 4 as page union all
select 5 as empid, 52 as staffid, '2' as companyid, '1' as region, '2a' as branchid, 4 as page union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid, 4 as page

Criteria is this:
1. The data are sorted by empid and staffid.
2. The data are page incremently everytime the companyid, regionid and branchid changes.

thanks in advance again...

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 18:11:32

Try this:


SELECT * into #foo FROM (
select 1 as empid, 11 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 1 as empid, 12 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 1 as empid, 13 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 21 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 22 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 23 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 3 as empid, 31 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 3 as empid, 32 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 3 as empid, 33 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 4 as empid, 41 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 4 as empid, 42 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 4 as empid, 43 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid union all
select 5 as empid, 52 as staffid, '2' as companyid, '1' as region, '2a' as branchid union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid  ) a

;WITH ctePaging AS 
	( SELECT DISTINCT
		 paging = ROW_NUMBER() OVER 
			(
				ORDER BY companyid,region,branchid)				 	
		,companyid
		,region
		,branchid
	  FROM #foo
	  GROUP BY companyid,region,branchid
	
	)
SELECT DISTINCT a.*,b.paging
FROM #foo a inner join ctePaging b
	 on a.companyid = b.companyid
	and a.region = b.region
	and a.branchid = b.branchid

 

drop table #foo

EDIT: Sorry..just noticed it was SQL 2000...let me hit you back in a minute.

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 18:14:19

Sorry--here is a version for SQL 2000


SELECT * into #foo FROM (
select 1 as empid, 11 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 1 as empid, 12 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 1 as empid, 13 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 21 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 22 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 23 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 3 as empid, 31 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 3 as empid, 32 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 3 as empid, 33 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 4 as empid, 41 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 4 as empid, 42 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 4 as empid, 43 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid union all
select 5 as empid, 52 as staffid, '2' as companyid, '1' as region, '2a' as branchid union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid  ) a

 
	 SELECT DISTINCT
	     identity(int,1,1) as paging		 	
		,companyid
		,region
		,branchid
	  into #paging
	  FROM #foo
	  GROUP BY companyid,region,branchid
	
	 
SELECT DISTINCT a.*,b.paging
FROM #foo a inner join #paging b
	 on a.companyid = b.companyid
	and a.region = b.region
	and a.branchid = b.branchid

 

drop table #foo
drop table #paging

Poor planning on your part does not constitute an emergency on my part.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2011-02-09 : 02:19:58

thank you, thank you, thank you very much.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2011-02-11 : 12:31:26

Good day, can I follow-up? How about if the data goes like this?


select 1 as empid, 11 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 1 as empid, 12 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 1 as empid, 13 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 21 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 22 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 2 as empid, 23 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 3 as empid, 31 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 3 as empid, 32 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 3 as empid, 33 as staffid, '1' as companyid, '2' as region, '1a' as branchid union all
select 4 as empid, 41 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 4 as empid, 42 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 4 as empid, 43 as staffid, '2' as companyid, '1' as region, '1a' as branchid union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid union all
select 5 as empid, 52 as staffid, '2' as companyid, '1' as region, '2a' as branchid union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid union all

select 6 as empid, 61 as staffid, '1' as companyid, '1' as region, '1a' as branchid union all
select 6 as empid, 61 as staffid, '1' as companyid, '1' as region, '1a' as branchid

Result should be like this...


select 1 as empid, 11 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 1 as empid, 12 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 1 as empid, 13 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 2 as empid, 21 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 2 as empid, 22 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 2 as empid, 23 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 1 as page union all
select 3 as empid, 31 as staffid, '1' as companyid, '2' as region, '1a' as branchid, 2 as page union all
select 3 as empid, 32 as staffid, '1' as companyid, '2' as region, '1a' as branchid, 2 as page union all
select 3 as empid, 33 as staffid, '1' as companyid, '2' as region, '1a' as branchid, 2 as page union all
select 4 as empid, 41 as staffid, '2' as companyid, '1' as region, '1a' as branchid, 3 as page union all
select 4 as empid, 42 as staffid, '2' as companyid, '1' as region, '1a' as branchid, 3 as page union all
select 4 as empid, 43 as staffid, '2' as companyid, '1' as region, '1a' as branchid, 3 as page union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid, 4 as page union all
select 5 as empid, 52 as staffid, '2' as companyid, '1' as region, '2a' as branchid, 4 as page union all
select 5 as empid, 51 as staffid, '2' as companyid, '1' as region, '2a' as branchid, 4 as page union all

select 6 as empid, 61 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 5 as page union all
select 6 as empid, 61 as staffid, '1' as companyid, '1' as region, '1a' as branchid, 5 as page

Thanks in advance.

.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-11 : 14:16:43

well you disagreed the second point of your criteria yourselves .. perhaps you should redefine the criteria first other wise the suggested query will fail for any other instance of the data sequence

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-11 : 14:29:31

quote:
Originally posted by MIK_2008

well you disagreed the second point of your criteria yourselves .. perhaps you should redefine the criteria first other wise the suggested query will fail for any other instance of the data sequence

Yep. Can't change the rules now!

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources