ORDER BY

  • This is one of those questions the professors in college speak of when they grade you. The "good profs" (arguably, some may not think them good, I do, but as stated above it is my opinion) toss this question out and do not use it as part of your grade.

    Humbly, though, I must admit, I was not aware of the "order by" flaw in 2000. Having not written a query that incorrectly uses the order by, there may be some userfulness here.

    Jamie

  • Second attempt to post. Page cannot be display on first.

    On my SQL Server 2005 system I was able to run the query:

    select createdate,*

    from dbo.aspnet_membership

    order by createdate desc

    without error. The createdate column is the first column in the result set and also appears in the column list produced by the "*" in its correct location. The result set is in reverse createdate order (most recent date first).

    Unfortunately, this database was ported from SQL 2000 and the compatiability mode was not raised to 9 on this database so my test did not produce the error I had expected. However, whenever the answer varies by version the question should specify the version. Before long we will have 2008 to also consider but most SQL Server is still on 2K and probably will be for at least another year, maybe two.

    -- Mark D Powell --

  • This is actually much cooler than it looks - a poster above noted this but I did not understand until I tested...

    If you specify a column alias that matches a column name that is output by the "*" wildcard, SQL Server 2000 will consistently use the aliased column in preference to the actual table column in the order by, even if you qualify the column name in the ORDER BY clause!

    This is crazy behaviour, I can see why they dropped support for it in 2005 (compatibility 90):

    I agree, this is crazy behavior, and it only gets more interesting. I modified my script, the alias is used for the ORDER BY clause, the actual field is used for the WHERE clause.

    USE [master]

    GO

    -- Create Sample Database

    CREATE DATABASE [QotdTestDB]

    GO

    -- Set Compatibility to 80

    EXEC dbo.sp_dbcmptlevel @dbname=N'QotdTestDB', @new_cmptlevel=80

    GO

    Use QotdTestDB

    Go

    Create Table Employee (

    EmpID INT,

    EmpName Varchar(20),

    Start_Date DateTime,

    End_Date DateTime

    )

    GO

    Insert Employee (EmpID, EmpName, Start_Date, End_Date)

    Select 1, 'Adams', Datediff(dd,1,GetDate()), DateDiff(dd,-1,GetDate()) Union

    Select 2, 'Davis', Datediff(dd,2,GetDate()), DateDiff(dd,-2,GetDate()) Union

    Select 3, 'Jones', Datediff(dd,3,GetDate()), DateDiff(dd,-3,GetDate()) Union

    Select 4, 'Smith', Datediff(dd,4,GetDate()), DateDiff(dd,-4,GetDate())

    Go

    -- This query will run, but the answers are not what I expected

    Select End_Date as Start_Date, * from Employee

    Order by [Employee].[Start_Date] desc

    GO

    Select End_date as Start_Date, * from Employee

    Where Start_Date < GetDate()-3 -- This uses Start_Date in the table

    Order by [Employee].[Start_Date] desc -- This uses Start_Date the alias

    GO

    -- Drop the test database

    Use [Master]

    DROP Database [QotdTestDB]

    GO

  • Like many others, I missed the question, but I learned something today. In my world, that evens the score.

    In hindsight, it's a good question. I'm glad that compatibility 90 "fixes the glitch."

  • I was able to successfuly execute the select statement

    select HireDate, * from employees order by HireDate desc

    on Northwind employees tables in SQL 2005 and SQL 2000 (using both management consoles).

    Why the answer specified in choice (a) is wrong? :unsure:

    Only because it will not run successfully under very specific conditions....SQL2K DB on SQL2K5 DB platform with SQL2K version...Can Upgrade Advisor catch this? Because I sure didn't!

  • Hi,

    Its work fine with server 2000 but not with 2005

  • Oh Dear!

    I have tested the same query in SQL Server 2000 / 2005. It is working fine. Option 1 is the right answer. May be this question should have been written more clearly.

    Thanks

    Un happy Member

    Kindest Regards,

    Atif Saeed Khan

  • Its work fine with 2000 but not 2005.Before any question specify sql server version:)

  • barb.w:

    answer (a) is wrong not in the context of the SQL version you are running but in the syntax of the SQL used in SQL Server 2000 and 2005(mode 80).:)

    The ORDER BY clause should use the table column i.e. the one from the * part of the select but it is using the expression referenced column.

    As pointed out a where clause will use the correct column.

    This will only lead to confusion when writing queries.

    Selects should only return a set of uniquely named columns, although SQL Server 2000 will allow otherwise.

    The order by clause should use a set of uniquely named columns.

    This is best practice - SQL Server 2000 is sloppy! 😛

    Hiding under a desk from SSIS Implemenation Work :crazy:

  • Wow. Seven pages already!

    OK, a summary. What this question has illustrated:

    1. There are practical version differences between SQL2K and SQL2K5 regarding T-SQL handling.

    2. If you work on "theoretically correct" and don't then test, you'll get caught out at some point.

    3. QOTD authors are fallible.

    4. The devil's in the detail.

    5. A DBA's capacity for debate is seemingly boundless 😉

    Semper in excretia, suus solum profundum variat

  • I also answered based upon SQL Server 2000.

    Please consider environment before printing this message 🙂

  • HI All

    The query will work without any error. the suggested answer is wrong

    the answer for the question is

    Retrieve all columns additionally the date column as a atfirst column

    by all

    shamsudheen

  • Given Ans is incorrect query will work without any error, Option "A" is correct.

    On getting incorrect ans, I have tested on both Sql-Server 2000 and 2005; this works properly, without any error.

    This fails on Oracle, but we work on SQL-Server.

    This is a contradictory question.

  • People should read all the previous posts before commenting.

    The last two guys obviously have learned nothing!:hehe:

    SQL server 2000 is flawed and SQL server 2005 (mode 80) is flawed.

    Mode 90 SQL2005 implements SQL correctly therfore the correct answer is (d)!

    --Shaun

    Hiding under a desk from SSIS Implemenation Work :crazy:

  • Why such a contradictory question in a forum like SQL Server Central. its really upsets me.:D

    Please consider environment before printing this message 🙂

Viewing 15 posts - 61 through 75 (of 182 total)

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