Order By Question

  • 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

  • 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

  • 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

  • Is this ORDER BY statement works ..... not for me...

    ORDER BY (case when SequenceNumber >= @lastSeqNumber then 0 else 1 end ), SequenceNumber

  • 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