March 31, 2008 at 9:06 am
In Sql Server 2000 it is working successfully
March 31, 2008 at 9:08 am
We should all quit complaining about this one, and chalk it up to a learning experience (read the question and check it out if it looks too obvious, like this one did) - everyone who posted a response (including me) got as many points for just posting a reply (or more if you posted more than one reply) ... :blink:
March 31, 2008 at 9:12 am
Ok, Shaun, i just checked and my SQL 2K5 has 80 compatibility like you said, but it works in 2k, so the suggested answer isn't correct across platforms.
so what happens with the points?
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]
March 31, 2008 at 9:12 am
OK, so we all learned something about SS 2005 using level 9.0 compatibility. But this QOD category was T-SQL, not SS 2005.
IMHO when only 1/4 of the members answer correctly, it is a bad question, just like the upgrade QOD from last week.
March 31, 2008 at 9:17 am
I just ran a similar query on a SQL 2000 database and it worked. That is the way I answered.
I see that others have indicated that for SQL 2005 databases you get an error.
The question then is ambiguous. Both answers should be allowed.
March 31, 2008 at 9:21 am
another wrong answer. I ran the query (one very similiar actually) and it runs fine in developers edition.
AW
March 31, 2008 at 9:26 am
The point is;-
2000/mode 8 is flawed and should report an error, it does not. 😀
ORDER BY clause should require unique column naming in the select
or
as suggested in the answer the table name needs to proceed the column name
e.g. employee.from_date to make it specific.
Its SQL best practice (2005/ mode 9 got it right) not flawed SQL as in 2000/mode 8. 🙂
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
March 31, 2008 at 9:37 am
[font="Verdana"]First time, long time. I agree with post#476997 (as well as with Shaun Tzu's wise thoughts :)). We should all get into the practice of qualifying our references to eliminate ambiguity. Never thought I would learn so much from a "wrong" answer![/font]
March 31, 2008 at 9:46 am
Try this in SQL2k or equivalent mode:
SELECT ShippedDate AS [OrderDate], * FROM Northwind.dbo.Orders o ORDER BY o.OrderDate DESC
which has a table name (or rather alias) preceeding the column name. If you were caught out with the QOD, which I was, then this nasty little query might help to explain why it's changed in 2k5.
Edit: the result set is ordered by ShippedDate.
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 9:51 am
Answers that are based on theory but not borne out by empirical results are flawed either in the way the question is posed or the theory itself.
Sorry, but I completely disagree with the question and answer as posed.
March 31, 2008 at 10:00 am
AF - I agree it was a poorly presented question. 🙁
(should have stated 2005/Mode 9 compatible).
But what a way to learn! 😉
You learn more from your defeats that from your victories, provided you survive. 😀
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
March 31, 2008 at 10:19 am
My gut reaction when I first saw this was to choose the "correct" message, then I tested this on my SQL 2005 and SQL 2000 servers, and both times it ran the query without an error. I thought I had learned something, only to find out I had more to learn.
For what its worth, this code on SQL 2005 illustrates exactly what happens with the compatibility levels.
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
)
GO
Insert Employee (EmpID, EmpName, Start_Date)
Select 1, 'Jones', GetDate() - 3 Union
Select 2, 'Smith', GetDate() - 4 Union
Select 3, 'Adams', GetDate() - 1 Union
Select 4, 'Davis', GetDate() - 2
Go
-- This will work
Select Start_date, * from Employee order by Start_date desc
GO
-- Set Compatibility level to 90
EXEC dbo.sp_dbcmptlevel @dbname=N'QotdTestDB', @new_cmptlevel=90
GO
-- This will not work
Select Start_date, * from Employee order by Start_date desc
Go
-- Drop the test database
Use [Master]
DROP Database [QotdTestDB]
GO
March 31, 2008 at 10:25 am
I think the QOD submitter would do well to actually test the scenario. The first answer is correct, i.e. all rows and columns in descending start_date order with start_date as the first column. It worked fine in both SQL 2000 and SQL 2005.
I used the following since I don't have an employees table:
select billeddate, * from araccountadjustment order by billeddate desc
It should be noted that the billeddate column appeared in the result set twice.
"Beliefs" get in the way of learning.
March 31, 2008 at 10:35 am
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):
CREATE TABLE #TestTable (SomeNumber Int)
INSERT INTO #TestTable SELECT 1
INSERT INTO #TestTable SELECT 2
INSERT INTO #TestTable SELECT 3
INSERT INTO #TestTable SELECT 4
INSERT INTO #TestTable SELECT 5
--here it will order by the aliased "SomeNumber" column, despite the ORDER BY clause being qualified to the table name - strange...
SELECT -SomeNumber As SomeNumber, *
FROM #TestTable T
ORDER BY T.SomeNumber ASC
--you might think that it was because the aliased column was the first with that name in the resultset - but no!
SELECT *, -SomeNumber AS SomeNumber
FROM #TestTable T
ORDER BY T.SomeNumber ASC
--only by removing this column altogether do we get the expected order by "T.Somenumber" result.
SELECT *--, -SomeNumber AS SomeNumber
FROM #TestTable T
ORDER BY T.SomeNumber ASC
DROP TABLE #TestTable
This is not an argument one way or the other about the points (I feel the debate has started to smell a little stale), but just an interesting side-note!
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 10:35 am
Whoops. My bad. The database I happened to be connected to is the only one out of about 30 with compatibility level 80.
"Beliefs" get in the way of learning.
Viewing 15 posts - 46 through 60 (of 182 total)
You must be logged in to reply to this topic. Login to reply