Post reply

ORDER BY

  • March 31, 2008 at 2:24 am

    #795800

    Obviously a version specific question, without specifying the version doh!

    Let's shoot/maim/harrass (delete as appropriate to your annoyance level) the question poster. 😀

    This is an IT forum - precision is everything - ambiguity leads to annoyed members.

    --Shaun

    Hiding under a desk from SSIS Implemenation Work :crazy:

  • March 31, 2008 at 3:00 am

    #795808

    Since the answer you have is incorrect for SQL 2000, and you never specified which version, can we have our points back please?

  • March 31, 2008 at 3:09 am

    #795811

    I tried the query on SQL SERVER 2005 and it ran fine with giving result as specified in choice (a).

  • March 31, 2008 at 3:17 am

    #795812

    Hi,

    The current compatibility level is 90.

    Msg 209, Level 16, State 1, Line 1

    Ambiguous column name 'PlanEnt'.

    This is my check....

  • March 31, 2008 at 3:22 am

    #795816

    If it is SQL 2000 then Correct ans is :-

    All columns and all rows from the employee table in descending start_date order, with the start_date as the first column

  • March 31, 2008 at 3:25 am

    #795818

    Hi All,

    So the question forgot to mention which version of SQL Server you are running, and which compatibility level. Obviously 2000 is now so out of date only us poor fools are still running it! 😉

    But I have learnt something new!

    Something that I know to work in 2000, won't work in 2005 (when set to 2005 compatibility) - which afterall is one of the reasons we have these questions!

    But of course I want my points, like everyone else...

    Cheers,

    Rodders...

  • March 31, 2008 at 3:55 am

    #795827

    Works just fine in SQL 2000.

    If you want to limit the question to SQL 2005 it should be stated.

  • March 31, 2008 at 4:09 am

    #795832

    For SQL Server 2000, none of the answers are quite correct, because the closest answer (A) misses the fact that the column is not just at the start, but also wherever it is in the table definition too. (if the table hass three columns, and this column is the third, then the column will be the first and last column of the results)

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • March 31, 2008 at 4:18 am

    #795835

    And there was I thinking that this was a nice simple question about one of the many pitfalls of using *, and suddenly find it's also a compatibility issue!

    Once again, QOTD teaches something that probably wasn't intended. 🙂

    Derek

  • March 31, 2008 at 4:20 am

    #795836

    It works fine in 2000. Arghhh credits lost :doze:

  • March 31, 2008 at 4:41 am

    #795841

    kevriley (3/29/2008)

    Comments posted to this topic are about the item

    Kev

    Try this in a SQL2k query window:

    select OrderDate, * from Northwind.dbo.Orders order by OrderDate desc

    Yes of course it works. Column OrderDate appears twice in the output, with the first occurrence being the first column. It certainly does not generate an error. Has anybody tried this in 2k5 yet?

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • March 31, 2008 at 4:42 am

    #795842

    dragos_sv (3/31/2008)

    i do not think it's a matter of sql server version. ideally you/we should be able to answer without running the command.

    but if you still want to run it, no one prevents you from adapting the script to match your (test) environment, table names, column names... etc

    dragos

    you are right dragos..I did it the wright way..but i was shocked to see that i was wrong as i was damn sure this one, then i just glanced thru this discussion to discover that I gave the wright answer:)

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • March 31, 2008 at 4:45 am

    #795843

    Chris Morris (3/31/2008)

    kevriley (3/29/2008)

    Comments posted to this topic are about the item

    Kev

    Try this in a SQL2k query window:

    select OrderDate, * from Northwind.dbo.Orders order by OrderDate desc

    Yes of course it works. Column OrderDate appears twice in the output, with the first occurrence being the first column. It certainly does not generate an error. Has anybody tried this in 2k5 yet?

    Cheers

    ChrisM

    Not quite, but close enough. I used a test database on my SQL2K5 server, and the query worked fine. Looked at the compatibility mode and it was set for SQL2K, so changed it to SQL2K5 and the query then failed, giving the error as expected in the QOTD answers.

    Semper in excretia, suus solum profundum variat

  • March 31, 2008 at 5:29 am

    #795852

    I'm not sure how I could be wrong. I constantly do this as many of our tables are so wide that it takes a while to find the column - what the question should say is that the column is repeated and SQL places it as EXPR1 elsewhere in the list. But wrong, no way! I think the bad questions are multiplying. The easy questions never seem to have an choice that shows the correct answer. There is something nebulous about answering questions and as I have been doing this for a while now, I can see that if all the answers appear incorrect, there really is no correct answer and I am going to get the question wrong if I answer it.

    Jamie

  • March 31, 2008 at 6:27 am

    #795882

    I tried it - works for me on SQL Server 2000 Advanced Server. Didn't realise it was a problem on 2005 though, so this is a useful discussion as a SQL upgrade may mean queries may need to be rewritten. I guess you could argue that there should not be queries defined with select * that are not ad-hoc though.

    Geoff.

Viewing 15 posts - 16 through 30 (of 182 total)

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