Order By question - 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 By question

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-12 : 23:05:04

Ok, this is more complex than just a regular ole Order By. I have a table called VideoSegments. It contains a SegmentID(PK), a URL and Sequence Number
So, data usually looks like this
PK | URL | Sequence
1 | 1.wmv | 1
3 | 2.wmv | 2
4 | 3.wmv | 3

And I order by the Sequence number...no problems. When the playlist is constructed, it orders them like that.

But here is the problem. Say the person is watching the video and leaves on 2.wmv. I can and do capture that info...and what I want to do is when they play th video the next time, I want to display the info like so

PK | URL | Sequence
3 | 2.wmv | 1
4 | 3.wmv | 2
1 | 1.wmv | 3

See, I can't really change the sequnce number in the DB, but when I am constructing the SQL statement, can I somehow change the Sequence number and get what I want? I know its a bit complex, but any help would be greatly appreciated. Thanks alot

SELECT *
FROM Segment
WHERE (VideoID = @VideoID)
ORDER BY SequenceNumber

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-12 : 23:31:15

Declare @orderBy varchar(100)
Set @orderBy = '2,3,1'

Select *
FROM Segment
WHERE (VideoID = @VideoID)
ORDER BY isnull(nullif(charindex((','+convert(varchar,sequenceNumber)+','), (','+@orderBy+','),0),len(@orderBy)+2)

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 23:33:45

What info do you have for the query?
If you get the seq no you want to start in @seq then

select PK, URL, Sequence = case when Sequence >= @Seq then Sequence - @Seq + 1 else Sequence + @seq end
from tbl
order by Sequence

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-12 : 23:39:30

Well, I dont "know" what the order by is. Basically, if I get @SegmentID I want to start with that segmentID and display the rest after it

SegmentID(PK) | URL | Sequence
1 | 1.wmv | 1
3 | 2.wmv | 2
4 | 3.wmv | 3
5 | 4.wmv | 4
6 | 5.wmv | 5

So, I get the above back in my query, and I pass in an @SegmentID of 4, I need to construct a query that changes the Order By to
4 | 3.wmv | 1
5 | 4.wmv | 2
6 | 5.wmv | 3
1 | 1.wmv | 4
3 | 2.wmv | 5

I dont even know if its possible, I am grabbing at straws a bit here...

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-12 : 23:49:49

quote:
Originally posted by Seventhnight

Declare @orderBy varchar(100)
Set @orderBy = '2,3,1'

Select *
FROM Segment
WHERE (VideoID = @VideoID)
ORDER BY isnull(nullif(charindex((','+convert(varchar,sequenceNumber)+','), (','+@orderBy+','),0),len(@orderBy)+2)

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Hey SeventhKnight,
Well, this works, but I have a question...I guess my main problem is how do I construct that @OrderBy? That is the part I can't get really...Thanks for any help!!

Harry C

P.S- my 6 month old just woke up...I may not be back for an hour or so..Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-13 : 01:24:50

But you still want it ordered by the sequence?
The query I gave assume you had the sequnce - if you have the segment you can get the sequemce that goes with it but you could find it in the query if you wish

select PK, URL, Sequence = case when Sequence >= lastseq.Sequence then Sequence - lastseq.Sequence + 1 else Sequence + lastseq.Sequence end
from tbl
cross join (select Sequence from VideoSegments where PK = @SegmentID) lastseq
order by Sequence

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-13 : 09:50:09

quote:
Originally posted by nr

But you still want it ordered by the sequence?
The query I gave assume you had the sequnce - if you have the segment you can get the sequemce that goes with it but you could find it in the query if you wish

select PK, URL, Sequence = case when Sequence >= lastseq.Sequence then Sequence - lastseq.Sequence + 1 else Sequence + lastseq.Sequence end
from tbl
cross join (select Sequence from VideoSegments where PK = @SegmentID) lastseq
order by Sequence

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

I will try that today and post back the results later on. Thanks nr!

HC

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-13 : 16:34:50

Either one of these two worked for me. They were bits and pieces I got from another board. Thank you so much for the help, it is very appreciated!!

SELECT *
FROM Segment
WHERE (VideoID = @VideoID)
ORDER BY (case when SequenceNumber >= @lastSeqNumber then 0 else 1 end ), SequenceNumber

SELECT
( case when SequenceNumber >= @LastSeqNumber
then SequenceNumber - @LastSeqNumber + 1
else @MaxSeqNumber + SequenceNumber - @LastSeqNumber + 1
end ) as ReplacementSequenceNumber
, *
FROM Segment
WHERE (VideoID = @VideoID)
ORDER BY 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources