Order by with specific criteria

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 by with specific criteria

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2008-03-14 : 03:37:16

Could somebody help me with this query...

Here's the sample data:



select t.*
from (
	select 1 as tID, 1 as shift, cast('2008-03-09' as datetime) as proddate, 1 as qty union all
	select 2 as tID, 1 as shift, cast('2008-03-11' as datetime) as proddate, 3 as qty union all
	select 3 as tID, 1 as shift, cast('2008-03-09' as datetime) as proddate, 4 as qty union all
	select 4 as tID, 2 as shift, cast('2008-03-10' as datetime) as proddate, 5 as qty union all
	select 5 as tID, 2 as shift, cast('2008-03-11' as datetime) as proddate, 6 as qty union all
	select 6 as tID, 1 as shift, cast('2008-03-10' as datetime) as proddate, 2 as qty union all
	select 7 as tID, 1 as shift, cast('2008-03-14' as datetime) as proddate, 7 as qty union all
	select 8 as tID, 1 as shift, cast('2008-03-15' as datetime) as proddate, 8 as qty union all
	select 9 as tID, 1 as shift, cast('2008-03-09' as datetime) as proddate, 9 as qty union all
	select 10 as tID, 1 as shift, cast('2008-03-10' as datetime) as proddate, 10 as qty
	) as t	
order by t.shift asc, t.proddate asc, t.tID asc

And here's the desired result...




select page1.*
from (
	select 1 as tID, 1 as shift, cast('2008-03-09' as datetime) as proddate, 1 as qty union all
	select 6 as tID, 1 as shift, cast('2008-03-10' as datetime) as proddate, 2 as qty union all
	select 2 as tID, 1 as shift, cast('2008-03-11' as datetime) as proddate, 3 as qty union all
	select 7 as tID, 1 as shift, cast('2008-03-14' as datetime) as proddate, 7 as qty union all
	select 8 as tID, 1 as shift, cast('2008-03-15' as datetime) as proddate, 8 as qty 	
	) page1
union all
select page2.*
from (
	select 3 as tID, 1 as shift, cast('2008-03-09' as datetime) as proddate, 4 as qty union all
	select 10 as tID, 1 as shift, cast('2008-03-10' as datetime) as proddate, 10 as qty 
	) as page2
union all
select page3.*
from (
	select 9 as tID, 1 as shift, cast('2008-03-09' as datetime) as proddate, 9 as qty 
	) as page3
union all
select page4.*
from (
	select 4 as tID, 2 as shift, cast('2008-03-10' as datetime) as proddate, 5 as qty union all
	select 5 as tID, 2 as shift, cast('2008-03-11' as datetime) as proddate, 6 as qty 	
	) as page4

Criteria:
This is just like paging each data by week. At first, the datas are ordered by shift, proddate, tID. Say for instance week 09March2008-15March2008. On this sample data, the paging took first by shift. Duplicate records for the particular date belongs to another page.

Any help will be greatly appreciated.

THANKS!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-14 : 04:28:39

[code]DECLARE @sample TABLE (tID int, shift int, proddate datetime, qty int)
INSERT INTO @sample
SELECT t.*
FROM (
SELECT 1 AS tID, 1 AS shift, CAST('2008-03-09' AS datetime) AS proddate, 1 AS qty UNION ALL
SELECT 2 AS tID, 1 AS shift, CAST('2008-03-11' AS datetime) AS proddate, 3 AS qty UNION ALL
SELECT 3 AS tID, 1 AS shift, CAST('2008-03-09' AS datetime) AS proddate, 4 AS qty UNION ALL
SELECT 4 AS tID, 2 AS shift, CAST('2008-03-10' AS datetime) AS proddate, 5 AS qty UNION ALL
SELECT 5 AS tID, 2 AS shift, CAST('2008-03-11' AS datetime) AS proddate, 6 AS qty UNION ALL
SELECT 6 AS tID, 1 AS shift, CAST('2008-03-10' AS datetime) AS proddate, 2 AS qty UNION ALL
SELECT 7 AS tID, 1 AS shift, CAST('2008-03-14' AS datetime) AS proddate, 7 AS qty UNION ALL
SELECT 8 AS tID, 1 AS shift, CAST('2008-03-15' AS datetime) AS proddate, 8 AS qty UNION ALL
SELECT 9 AS tID, 1 AS shift, CAST('2008-03-09' AS datetime) AS proddate, 9 AS qty UNION ALL
SELECT 10 AS tID, 1 AS shift, CAST('2008-03-10' AS datetime) AS proddate, 10 AS qty
) AS t
ORDER BY t.shift asc, t.proddate asc, t.tID asc

SELECT *, seq = (SELECT COUNT(*) FROM @sample x WHERE x.proddate = s.proddate AND x.shift <= s.shift AND x.tID <= s.tID)
FROM @sample s
ORDER BY shift, seq, proddate

/*
tID shift proddate qty seq
----------- ----------- ---------- ----------- -----------
1 1 2008-03-09 1 1
6 1 2008-03-10 2 1
2 1 2008-03-11 3 1
7 1 2008-03-14 7 1
8 1 2008-03-15 8 1
3 1 2008-03-09 4 2
10 1 2008-03-10 10 2
9 1 2008-03-09 9 3
4 2 2008-03-10 5 1
5 2 2008-03-11 6 2

(10 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2008-03-14 : 05:06:31

Thank you very much KH :-)

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...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources