Possible bug in the ORDER BY clause of SQL Server
-
Instead of me retyping my entire case, please refer to my blog entry (http://dotnet.org.za/craign/archive/2005/11/04/47987.aspx).
Any help will be appreciated.
-
Craig
I think its just some funny behaviiour you get with some non-standard i.e. non Ansi-Compliant features if you change your query to
SELECT f.[Id],
[Descriptions] = CASE
WHEN b.[Description] IS NOT NULL THEN b.[Description]
ELSE f.[Description]
END
FROM [dbo].[Foo] AS f
LEFT OUTER JOIN [dbo].[Bar] AS b
ON f.[Id] = b.[Id]
ORDER BY f.[Description] ASC
this will work and all you've done is change the name of the column for the case statement from Description to Descriptions.
Someone alse on the forum may be able to explain why this is but you do sometimes get some unexpected behaviour from non-standard features.hth
David
-
The point is that I want the column named "Description".
-
Sorry I was just pointing out that as you have used f.Description in the CASE statement that it was ordering on the Description column unless you changed the name and when you you use some non-standard features you can get some very unexpected behaviour.
This will make sure it orders as you wish
SELECT f.[Id],
[Description] = CASE
WHEN b.[Description] IS NOT NULL THEN b.[Description]
ELSE f.[Description]
END
FROM [dbo].[Foo] AS f
LEFT OUTER JOIN [dbo].[Bar] AS b
ON f.[Id] = b.[Id]
ORDER BY CASE
WHEN b.[Description] IS NOT NULL THEN f.[Description]
ELSE f.[Description]
END
hth
David
-
Do not specify prefix in ORDER BY clause.
SELECT f.[Id],
[Description] = CASE
WHEN b.[Description] IS NOT NULL THEN b.[Description]
ELSE f.[Description]
END,
[Foo_Description] = f.[Description]
FROM [dbo].[Foo] AS f
LEFT OUTER JOIN [dbo].[Bar] AS b
ON f.[Id] = b.[Id]
ORDER BY [Description] ASC
If there is no prefix optimizer searches for the column from ORDER BY in the following order:
1. returned resultset;
2. objects mentioned in the query.
As soon as there is a column [Description] in returned set it will be used for sorting.
If you'll rename the column you'll get the error:
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
because there are 'Description' columns in both tables.
And actually ISNULL(b.[Description], f.[Description]) is easier to read and faster to run than
CASE
WHEN b.[Description] IS NOT NULL THEN b.[Description]
ELSE f.[Description]
END
_____________
Code for TallyGenerator -
On the ISNULL() point I would agree and actually recommend using COALESCE() as its more standards compliant. But the actual point of the query is to sort by [dbo].[Foo].[Description] and NOT by the result set column named [Description].
-
Sorry, because you have [Description] in resultset it will always being used for ordering. It was long discussion about year ago about ANSI and implementation of its prescriptions by Microsoft.
Good or bad but this is it.
To separate [Description] to return and [Description] to use for ORDER BY use query with derived table:
SELECT Id, [Description]
FROM (SELECT f.[Id],
ISNULL(b.[Description], f.[Description]) as [Description],
f.[Description] as descr
FROM [dbo].[Foo] AS f
LEFT OUTER JOIN [dbo].[Bar] AS b ON f.[Id] = b.[Id]) DT
ORDER BY Descr ASC
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply