order by returning data in wrong order

  • I have a composite clustered index. The first column is an integer, the second column is a char(1). In the index definition, column_one is defined as DESC.

    The select list of the query is completely covered by the clustered index. When I execute the query with no 'order by' clause the data is returned in DESC sequence which is correct. When I execute the query with an 'order by' clause specifying column_one ASC, the data is returned in DESC sequence which is incorrect. If I execute the query with an 'order by' clause specifying column_one DESC, the data is returned in ASC sequence which is incorrect.

    Has anyone experienced this?

  • Hello dwo,

    Can you please post the actual DDL for the table and the index?  Sample data and the actual select statements you are using would also be helpful.

    Thanks

    Wayne

  • I guess there is confusion in What is ASC and DESC.

    When there is no order by SQL Server would return the rows in ASC. When we specify DESC then it will return Descending order.

    Check this

    SET NOCOUNT ON

    DECLARE @MyTable TABLE

    (

    myID INT PRIMARY KEY,

    MyDesc VARCHAR(10)

    )

    INSERT @MyTable

    SELECT 5, 'DESC5' UNION

    SELECT 3, 'DESC3' UNION

    SELECT 1, 'DESC1' UNION

    SELECT 4, 'DESC4' UNION

    SELECT 2, 'DESC2'

    SELECT * FROM @MyTable

    SELECT * FROM @MyTable ORDER BY myID

    SELECT * FROM @MyTable ORDER BY myID DESC

    Regards,
    gova

  • I don't understand the problem.

    I tried this:

    Create TABLE #MyTable (

     myID INT,

     MyDesc VARCHAR(10),

     CONSTRAINT PK_MyTable_1 PRIMARY KEY CLUSTERED (Myid DESC)

      )

    INSERT #MyTable

    SELECT 5, 'DESC5' UNION

    SELECT 3, 'DESC3' UNION

    SELECT 1, 'DESC1' UNION

    SELECT 4, 'DESC4' UNION

    SELECT 2, 'DESC2'

    SELECT * FROM #MyTable

    SELECT * FROM #MyTable ORDER BY myID

    SELECT * FROM #MyTable ORDER BY myID DESC

    DROP TABLE #MyTable

    Works perfect, returns lines in right order everytime.

    Check your query or data you've inserted.

    _____________
    Code for TallyGenerator

  • I understand very well the difference between ASC and DESC in an order by clause. Please don't insult my intelligence. You missed the point of the post. Try this:

    -----------------------------------------------------------

    -- Create Table

    -----------------------------------------------------------

    create table table_one

    (

      column_one int not null ,

      column_two char(1) collate sql_latin1_general_cp1_ci_as not null

    )

    go

    -----------------------------------------------------------

    -- Create Clustered Index

    -----------------------------------------------------------

    create clustered index cluster_001

    on table_one

    (column_one desc, column_two)

    go

    -----------------------------------------------------------

    -- Insert Test Data

    -----------------------------------------------------------

    insert table_one values (1,'a')

    insert table_one values (2,'b')

    insert table_one values (3,'c')

    insert table_one values (3,'k')

    insert table_one values (3,'l')

    insert table_one values (3,'x')

    insert table_one values (4,'d')

    insert table_one values (5,'e')

    insert table_one values (6,'f')

    insert table_one values (6,'m')

    insert table_one values (6,'n')

    insert table_one values (6,'x')

    insert table_one values (7,'g')

    insert table_one values (7,'s')

    insert table_one values (8,'h')

    insert table_one values (8,'q')

    insert table_one values (9,'i')

    insert table_one values (9,'o')

    insert table_one values (9,'p')

    insert table_one values (9,'x')

    insert table_one values (10,'j')

    insert table_one values (10,'p')

    go

    -----------------------------------------------------------

    -- No ORDER BY Clause

    -----------------------------------------------------------

    SELECT column_one, column_two      -- Ascending order returned

    FROM table_one                             -- No SORT in Execution Plan

    WHERE (column_one IN ('3','6','9'))

      and (column_two not  IN ('x'))

    -- order by column_one

    go

    -----------------------------------------------------------

    -- ORDER BY Clause on Column_One

    -----------------------------------------------------------

    SELECT column_one, column_two    -- Descending order returned

    FROM table_one                           -- No SORT in Execution Plan

    WHERE (column_one IN ('3','6','9'))

      and (column_two not  IN ('x'))

    order by column_one

    go

    -----------------------------------------------------------

    -- ORDER BY Clause on Column_One DESC

    -----------------------------------------------------------

    SELECT column_one, column_two    -- Ascending order returned

    FROM table_one                           -- No SORT in Execution Plan

    WHERE (column_one IN ('3','6','9'))

      and (column_two not  IN ('x'))

    order by column_one desc

    go

    -----------------------------------------------------------

    -- ORDER BY Clause on Column_One ASC

    -----------------------------------------------------------

    SELECT column_one, column_two    -- Descending order returned

    FROM table_one                           -- No SORT in Execution Plan

    WHERE (column_one IN ('3','6','9'))

      and (column_two not  IN ('x'))

    order by column_one asc

    go

    -----------------------------------------------------------

    -- No AND Clause

    -- No ORDER BY Clause

    -----------------------------------------------------------

    SELECT column_one, column_two    -- Descending order returned

    FROM table_one                           -- No SORT in Execution Plan

    WHERE (column_one IN ('3','6','9'))

    --  and (column_two not  IN ('x'))

    -- order by column_one

    go

    -----------------------------------------------------------

    -- No AND Clause

    -- ORDER BY Clause on Column_One

    -----------------------------------------------------------

    SELECT column_one, column_two    -- Ascending order returned

    FROM table_one                           -- No SORT in Execution Plan

    WHERE (column_one IN ('3','6','9'))

    --  and (column_two not  IN ('x'))

    order by column_one

    go

    -----------------------------------------------------------

    -- No AND Clause

    -- ORDER BY Clause on Column_One DESC

    -----------------------------------------------------------

    SELECT column_one, column_two    -- Descending order returned

    FROM table_one                           -- No SORT in Execution Plan

    WHERE (column_one IN ('3','6','9'))

    --  and (column_two not  IN ('x'))

    order by column_one desc

    go

    -----------------------------------------------------------

    -- No AND Clause

    -- ORDER BY Clause on Column_One ASC

    -----------------------------------------------------------

    SELECT column_one, column_two    -- Ascending order returned

    FROM table_one                           -- No SORT in Execution Plan

    WHERE (column_one IN ('3','6','9'))

    --  and (column_two not  IN ('x'))

    order by column_one asc

    go

     

  • That is extremely weird! And I don't think anyone was insulting your intelligence; you'd be suprised at what kind of questions get asked, and how are we to know how much someone knows when they don't provide a DDL. In this instance, this question was impossible to answer without the DDL.

    The problem is your clustered index. You made it descending for some reason. Essentially this causes "order by desc desc", which becomes the same as "order by asc"

    Make it a regular clustered index and you'll be fine.

    create clustered index cluster_001

    on table_one

    (column_one, column_two)

    go

    Signature is NULL

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply