February 12, 2005 at 9:17 pm
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
February 13, 2005 at 2:37 am
Given the sequence number always indicates the order, and effectively the list wraps back to the beginning, using the captured data of the last sequence number (@LastSeqNumber) on which video the user had been on,
you might get something like this
SELECT *
FROM Segment
WHERE (VideoID = @VideoID)
ORDER BY (case when SequenceNumber >= @lastSeqNumber then 0 else 1 end ), SequenceNumber
If want new sequence numbers, you would need to know how many records are present, and adjust the SequenceNumber by this offset.
two values to be passed or calculated are
@LastSeqNumber, @MaxSeqNumber
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
Cheers
Robert
February 13, 2005 at 2:41 pm
That works like a charm...Thank you so much
SELECT *
FROM Segment
WHERE (VideoID = @VideoID)
ORDER BY (case when SequenceNumber >= @lastSeqNumber then 0 else 1 end ), SequenceNumber
February 14, 2005 at 8:36 am
Is this ORDER BY statement works ..... not for me...
ORDER BY (case when SequenceNumber >= @lastSeqNumber then 0 else 1 end ), SequenceNumber
February 14, 2005 at 9:07 am
works fine for me...I actually had to edit the query a bit to get what I needed, but what posted does work...both methods actually...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply