wrap around ordering by a integer field

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)

wrap around ordering by a integer field

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2009-03-12 : 05:37:44

I am trying to retrieve records in a wrap around manner. the records have an number that I want to start at, get everything up to the last record, then add the results before the start no. eg.

1,2,3,4,5,6,7,8,9,10

If I want to start at 4 it should return

4,5,6,7,8,9,10,1,2,3

or 9

9,10,1,2,3,4,5,6,7,8

I though I might be able to use two IN commands but the results seem to be ordered by the primary key:


SELECT     *
FROM         exec_ext_Shows_Extra
WHERE     (UniqueID IN
                          (SELECT     UniqueID
                            FROM          exec_ext_Shows_Extra
                            WHERE      Interval > 4)) OR
                      (UniqueID IN
                          (SELECT     UniqueID
                            FROM          exec_ext_Shows_Extra
                            WHERE      Interval < 5))

always returns 1,2,3,4,5,6,7,8,9,10 where as I'd want it to return 5,6,7,8,9,10,1,2,3,4

Any ideas how/if this can be achieved without returning 2 resultsets?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 06:19:01

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
Number INT
)

INSERT @Sample
SELECT Number
FROM master..spt_values
WHERE Type = 'P'
AND Number BETWEEN 1 AND 10

-- Initialize user supplied parameter
DECLARE @StartID INT

SET @StartID = 5

SELECT Number
FROM @Sample
ORDER BY CASE
WHEN Number >= @StartID THEN 0
ELSE 1
END,
Number[/code]

E 12°55'05.63"
N 56°04'39.26"

chandan_joshi80
Starting Member

30 Posts

Posted - 2009-03-12 : 06:30:40

Declare @no varchar(3)
Declare @str varchar(50)
Declare @len int
Set @str = '10,20,30,40,50,60'
Set @no = '30'
Set @len = Len(@str)

Select Substring(@str,CharIndex(@no,@str),@len)+
Case When CharIndex(@no,@str)<> 1 Then ',' Else '' End+
Substring(@str,1,Case When CharIndex(@no,@str)= 1 Then 0
Else CharIndex(@no,@str)-2 End
)

chandan Joshi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources