A question on Ordering - 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

SQL Server Development (2000)

A question on Ordering

Author

Topic

brad_lucas
Starting Member

2 Posts

Posted - 2005-11-15 : 01:23:14

Hi all,

[edit: re-written to conform to this (http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx)]

How do I cycle through the recordset order.

CREATE TABLE tblCycle (
cycleID int IDENTITY (1, 1) NOT NULL ,
cycleName nvarchar (255) NOT NULL
)

INSERT INTO tblCycle (cycleName) VALUES ("Week One")
INSERT INTO tblCycle (cycleName) VALUES ("Week Two")
INSERT INTO tblCycle (cycleName) VALUES ("Week Three")
INSERT INTO tblCycle (cycleName) VALUES ("Week Four")

What I want to do is order the tblCycle data by whatever "cycle" we're currently in.

Here's the Stored Proc I've been toying with:

CREATE PROCEDURE prc_listCycle
(
@cycleID int
)
AS
SELECT * FROM tblCycle....

(Not very impressive huh!)

This is an example of what I hope to get out of it:

Week 1:
ID | Title
------------
1 | Week One
2 | Week Two
3 | Week Three
4 | Week Four

Week 2:
ID | Title
------------
2 | Week Two
3 | Week Three
4 | Week Four
1 | Week One

Note that all the records are still displayed, but the order has stepped one. 1 is now last, while 2 is first.

I hope this makes sense and that someone can offer any hints or tips on how I might go about doing this? Do I need to add another column to set the order? I really have no idea and any assistance would be greatly appreciated!

Many thanks
Brad Lucas

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-11-15 : 02:20:30

[code]
CREATE TABLE Cycle (
cycleID INT IDENTITY (1, 1) NOT NULL ,
cycleName NVARCHAR (255) NOT NULL
)
GO
INSERT INTO Cycle (cycleName) VALUES ('Week One')
INSERT INTO Cycle (cycleName) VALUES ('Week Two')
INSERT INTO Cycle (cycleName) VALUES ('Week Three')
INSERT INTO Cycle (cycleName) VALUES ('Week Four')
GO

CREATE PROCEDURE TESTListCycle

@cycleID INT

AS

SELECT
cycleID,
cycleName
FROM
Cycle
ORDER BY
CASE
WHEN CycleID < @cycleID THEN CycleId + 1000000
ELSE CycleID END
GO

EXEC TESTListCycle 1
EXEC TESTListCycle 2
EXEC TESTListCycle 3
EXEC TESTListCycle 4

GO
DROP PROCEDURE TESTListCycle
GO
DROP TABLE Cycle
GO
[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-15 : 02:25:08

Alternative solution


select 	cycleID, cycleName
from 	tblCycle
order by (cycleID + 4 - @cycleID) % 4

[KH]

brad_lucas
Starting Member

2 Posts

Posted - 2005-11-15 : 16:35:50

Hi guys,

Thanks for your assistance. KH - I think I'll go with your option, it looks easier. I would like to confirm something with you though. If the number of Cycles ever increase, do I simply alter the numbers in the Order By: For example:

ORDER BY (cycleID + 7 - @cycle) % 7

What I was thinking of doing was get the cycle Count automatically like this:


CREATE PROCEDURE getCycles

AS
Declare @cycle int
Declare @count int

--GET CURRENT CYCLE
Set @cycle = (SELECT TOP 1 cycle FROM tblCurrentCycle ORDER BY id DESC)

--COUNT CYCLES
Set @count = (SELECT COUNT(*)  FROM tblCycles)

--DISPLAY CYCLE IN ORDER OF CURRENT CYCLE
SELECT *
FROM tblCategories
ORDER BY (cycleID + @count - @cycle) % @count
GO

I haven't tested it extensivly yet, but it seems to work. Can you see any major floors in this?

Regards
Brad

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-11-15 : 19:20:24

If you're going to use that method, I would agree it's better to use the variable so you don't have to alter the stored procedure everytime the data changes.

That's why I picked a randomly high number.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-15 : 22:26:55

Brad,

Yes. It will work for any number of cycles

[KH]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources