SQL ORDER BY weirdness

I'm sure some SQL guru will laugh and kick Dummies books in my face, but what's the problem here? I decided to do a stored procedure to page results of forum topics using a cursor. (Before anyone tells me that's a bad idea, read this to see why using the cursor appears to be the best performing option.) Originally, I was getting topic records in a date range, using this SQL:

SELECT * FROM Topics
WHERE (ForumID = @ForumdID) AND ((LastPostTime > @TimeSpan) OR (Pinned = 1))
ORDER BY Pinned DESC, LastPostTime DESC

To populate the cursor, prior to the paging and such, my sproc uses this:

SELECT TopicID FROM Topics
WHERE ForumID = @ForumID
ORDER BY Pinned DESC, LastPostTime DESC

For some likely obvious reason I'm missing, it doesn't do the ordering for the Pinned column, which is a bit. Pinned topics should come first.

Tell me what I'm missing, and you get a free copy of the forums! ;)

17 Comments

  • To put it more in context... here's the entire sproc:





    CREATE PROCEDURE PagedTopics

    (

    @StartRow int,

    @PageSize int,

    @ForumID int,

    @Total int OUTPUT

    )



    AS



    DECLARE @PK int

    DECLARE @tblPK TABLE

    (

    PK int NOT NULL PRIMARY KEY

    )



    DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR

    SELECT TopicID FROM Topics WHERE ForumID = @ForumID ORDER BY Pinned DESC, LastPostTime DESC



    OPEN PagingCursor

    FETCH RELATIVE @StartRow FROM PagingCursor INTO @PK



    WHILE @PageSize > 0 AND @@FETCH_STATUS = 0

    BEGIN

    INSERT INTO @tblPK (PK) VALUES (@PK)

    FETCH NEXT FROM PagingCursor INTO @PK

    SET @PageSize = @PageSize - 1

    END



    SET @Total = @@CURSOR_ROWS



    CLOSE PagingCursor

    DEALLOCATE PagingCursor



    SELECT * FROM Topics JOIN @tblPK t ON Topics.TopicID = t.PK

    ORDER BY LastPostTime DESC

    GO

  • You can't order by a bit field, you need to change the table definition to an int and use 0 and -1



    I always avoid use of bit fields because of their strange behavior. Many other databases don't support the use of a bit field.



    They are a legacy from the Sybase days when disk space was at a premium

  • Jim, why can't one order by a bit field? It seems to be true but I was wondering if it's actually documented somewhere, because the ORDER BY clause of a SELECT statement only says that you can't order by ntext, text or image data. Nothing about a bit.



    I like to use the bit field for columns that are three state (on, off or unknown), they're nicer to work with than integer data, as they're always 0 or 1 (or NULL), not 0 or a non-zero value (1, -1, and so on). And they're faster than an int column with a check constraint.

  • I can't find anywhere documented that sorting on bit will not work. I tried it in SQL 2000 and 2005 and seems to work.



    if object_id('tblTest') is not null

    begin

    drop table tblTest;

    end



    create table tblTest

    (col1 int identity(1,1) not null primary key,

    col2 bit not null default 0);

    go



    insert into tblTest default values;

    insert into tblTest values (1);

    insert into tblTest default values;

    insert into tblTest values (1);

    insert into tblTest default values;

    insert into tblTest values (1);

    go



    select * from tblTest;

    go



    select * from tblTest order by col2 DESC;

    go



    DECLARE @PK int;

    DECLARE @BF int;

    DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR

    SELECT col1, col2 FROM tblTest ORDER BY col2 DESC;



    OPEN PagingCursor;

    FETCH RELATIVE 2 FROM PagingCursor INTO @PK, @BF;



    WHILE @@FETCH_STATUS = 0

    BEGIN

    SELECT @PK, @BF;

    FETCH NEXT FROM PagingCursor INTO @PK, @BF;

    END

    CLOSE PagingCursor;

    DEALLOCATE PagingCursor;





    Jeff, what's the exact SQL Server build version you are working with?

  • I may very well be incorrect in my original statment.



    I do know that I have had nothing but problems in using bit fields. Specifically Group By does not work properly.



    Some info at http://www.ssw.com.au/SSW/Standards/Rules/RulestoBetterSQLServerdatabases.aspx FAQ 203



    Access and SQL Server handle bit fields differently.



    Are you using SQLAdapter or OLEDBAdapter?



  • Update Topics

    set Pinned = 0

    where Pinned Is Null



    Change the Topics table def to define a default value 0 for Pinned and also state that the field cannot be Null.



    Then try it again.



    I still recommened that you avoid bit fields! They are not an ANSI standard.

  • Hi there,



    with regards to the codeproject articel you mentioned: From my/our experiences here, a CURSOR is about the worst performing method for large sets. The fastest method still is that method which the author so easily discarded and didn't even bother to test.



    Best regards,



    Marc

  • I think you guys got it wrong. I'm having the same issue - I can't use the ORDER BY clause when defining a cursor. Example:

    DECLARE c CURSOR FOR (SELECT * FROM table1 ORDER BY col1 ASC);

    SQL Server (2005 for me) takes a huge crap and says "Incorrect syntax near ORDER BY."

    I've found nothing else about this, either. Even in the MSDN help, the second example in the topic "DECLARE CURSOR" shows it use an ORDER BY clause in the cursor declaration.

    ..or maybe I'm wrong and it's something else. [shrug]

  • This is an item from 2004. :) These days we'd use CTE's in SQL 2005!

  • I figured it out. You can't use parentheses around the declared SQL statement. Ugh.

    Invalid: DECLARE c CURSOR FOR (SELECT * FROM table1 ORDER BY col1 ASC);

    Valid: DECLARE c CURSOR FOR SELECT * FROM table1 ORDER BY col1 ASC;

    Haha, I didn't even see the date - just came up in a Google search. Way to raise the dead! :p I'll do research on CTEs now.

  • Double-D, I think your discovery about parenthesis just saved me a lot of trouble debugging an "inexplicable" result! Thanks. (And thanks also to Google; I merely did a search on [SQL CURSOR "ORDER BY"]. Ugh indeed - what a useless error message.

  • Double-D saved me too, with the same odd error!

    The funniest thing is that I searched the same string as Paul did. :)

  • Thanks... it solve the problem here too..

  • Same again here! What a rubbish rubbish error. Should say something like: "Remove parenthesis from the query on your CURSOR declaration" :) Ahh...my kingdom for some friendly errors...

    Maybe even: "Remove parenthesis from the query on your CURSOR declaration while I make you a coffee, you poor, beaten man. And mind your language next time"

  • DoubleD saved me...thanks for the help!

  • DoubleD , fantastic mate.. you are a star.. saved hell out time

  • saved me too!

Comments have been disabled for this content.