MS SQL Server - Strange sorting behaviour sql 20004 SP4

Author

Strange sorting behaviour sql 20004 SP4

Anders

2006-01-30, 7:23 am

Hi (Using SQL server 2000 SP4)

I´m having some strange problems with sorting on a joined table which is not
in the result set

I have the following DB able structure: Clinic -> Address -> Country

The problem is i want a clinic list order by the Country.Name but the
Country.Name must not be represented in the resultset. But when i sort on
the Country.Name it instead sorts on Clinic.Name. If the Country.Name IS in
the resultset there is no problem. Case 1 works but is not what I want, Case
2 does not work but illustrates my problem.

Case 1 the Country.Name is IN the result field and the following lines is
last as aspected.

Case 2 i have out-commented the Country.Name in the result and the resultset
is as fare as I can see NOT sorted by the Country.Name but on the
Clinic.Name

1) WITH COUNTRY IN RESULT==============
========

SELECT [dbo].[CLINIC].[CLINICID] AS [Clinicid],
[dbo].[CLINIC].[NAME] AS [Name],
[dbo].[CLINIC].[ACTIVE] AS [Active],
[dbo].[COUNTRY].[NAME] As [CountryName]
FROM (( [dbo].[ADDRESS]

INNER JOIN [dbo].[CLINIC] ON
[dbo].[ADDRESS].[ADDRESSID]=[dbo].[CLINIC].[ADDRESSID])
INNER JOIN [dbo].[COUNTRY] ON
[dbo].[COUNTRY].[COUNTRYID]=[dbo].[ADDRESS].[COUNTRYID])

WHERE ( ( [dbo].[CLINIC].[NAME] LIKE '%as%' AND [dbo].[CLINIC].[ACTIVE] =
1))

ORDER BY [dbo].[COUNTRY].[NAME] DESC

GIVES
----------------
1025699121564 University of Glasgow 1 United Kingdom
1115700688084 Yasam Hospital 1 Turkey
1096443141284 Instituto Extremeno de Reproduccion Asistida 1 Spain
1086064864131 Centre for Assisted Reproduction Ltd 1 Singapore
1117706610535 IASO Hospital, IVF Center 1 Greece
1039603149037 Felicitas-Klinikka 1 Finland
1112076151301 Nova Vita Kliinik AS 1 Estonia
1110526994680 Aleksander Laschke 1 Denmark
1029752788631 KAS Herlev 1 Denmark

2) WITHOUT COUNTRY IN RESULT==============
========

SELECT [dbo].[CLINIC].[CLINICID] AS [Clinicid],
[dbo].[CLINIC].[NAME] AS [Name],
[dbo].[CLINIC].[ACTIVE] AS [Active]
--[dbo].[COUNTRY].[NAME] As [CountryName]
FROM (( [dbo].[ADDRESS]

INNER JOIN [dbo].[CLINIC] ON
[dbo].[ADDRESS].[ADDRESSID]=[dbo].[CLINIC].[ADDRESSID])
INNER JOIN [dbo].[COUNTRY] ON
[dbo].[COUNTRY].[COUNTRYID]=[dbo].[ADDRESS].[COUNTRYID])

WHERE ( ( [dbo].[CLINIC].[NAME] LIKE '%as%' AND [dbo].[CLINIC].[ACTIVE] =
1))

ORDER BY [dbo].[COUNTRY].[NAME] DESC

GIVES
----------------
1115700688084 Yasam Hospital 1
1025699121564 University of Glasgow 1
1112076151301 Nova Vita Kliinik AS 1
1029752788631 KAS Herlev 1
1096443141284 Instituto Extremeno de Reproduccion Asistida 1
1117706610535 IASO Hospital, IVF Center 1
1039603149037 Felicitas-Klinikka 1
1086064864131 Centre for Assisted Reproduction Ltd 1
1110526994680 Aleksander Laschke 1

Hugo Kornelis

2006-01-31, 8:23 pm

On Mon, 30 Jan 2006 12:46:06 +0100, Anders wrote:

>Hi (Using SQL server 2000 SP4)
>
>I´m having some strange problems with sorting on a joined table which is not
>in the result set
>
>I have the following DB able structure: Clinic -> Address -> Country
>
>The problem is i want a clinic list order by the Country.Name but the
>Country.Name must not be represented in the resultset. But when i sort on
>the Country.Name it instead sorts on Clinic.Name. If the Country.Name IS in
>the resultset there is no problem. Case 1 works but is not what I want, Case
>2 does not work but illustrates my problem.
>
>Case 1 the Country.Name is IN the result field and the following lines is
>last as aspected.
>
>Case 2 i have out-commented the Country.Name in the result and the resultset
>is as fare as I can see NOT sorted by the Country.Name but on the
>Clinic.Name

Hi Anders,

The reason is the somewhat wacky way in which a reference in the ORDER
BY list is used. There are no less than four ways to tell SQL Server how
to sort the results:

The first two are defined in the ANSI standard:

1. By ordinal position: "ORDER BY 2" means: order by the 2nd column in
the SELECT list,

2. By the alias of an expression in the SELECT list:
SELECT Column1 AS Yippee, ...
...
ORDER BY Yippee

The last two are non-ANSI-standard syntax extensions:

3. By an expression: "ORDER BY Column1 * Column2 - Column3".
The expression cannot reference aliases or ordinal positions of
columns in the SELECT list; it can, however, reference all columns
of all tables in the FROM clause.

4. By column name (this is a special case of #2 if the column name is
used in the WHERE clause without alias -i.e. the alias is equal to
the name of the column-, and a special case of #3 otherwise).

Number 4 is where the problems start. For instance, how should this
query be ordered:

SELECT X as Y, Y as X
FROM SomeTable
ORDER BY X

Is X a reference to the column SomeTable.X (aliased as Y in the result)
or to the alias X (which is assigned to SomeTable.Y)?

Of course, if we had used "ORDER BY SomeTable.X", there would have been
no ambiguity - and that is exactly where SQL Server 2000 displays the
wacky behaviour I mentioned before. In some casees, SQL Server 2000 will
completely disregard the prefixed table, use only the column name in the
ORDER BY clause - and interpret it according to rule #2 above!

>2) WITHOUT COUNTRY IN RESULT==============
========
>
>SELECT [dbo].[CLINIC].[CLINICID] AS [Clinicid],
>[dbo].[CLINIC].[NAME] AS [Name],
>[dbo].[CLINIC].[ACTIVE] AS [Active]
>--[dbo].[COUNTRY].[NAME] As [CountryName]
(snip)
>ORDER BY [dbo].[COUNTRY].[NAME] DESC

This is, apparently, such a case. SQL Server disregards dbo.Country, is
left with only NAME and matches that to the column alias used for
Clinic.Name.

Some workarounds are:

Change the alias for Clinic.Name:
[dbo].[CLINIC].[NAME] AS [ClinicName],

Include a dummy operation to force SQL Server to interpret the ORDER BY
argument as expression:
ORDER BY [dbo].[COUNTRY].[NAME] + '' DESC

Upgrade to SQL Server 2005 - I've read (somewhere - I've been hunting
for it but can't find it now) that SQL Server 2005 will always treat a
qualified name in the ORDER BY clause as a column name.

--
Hugo Kornelis, SQL Server MVP

Anders

2006-01-31, 8:23 pm

Thank you very much!

Superb help, thanks for the throughout explenation

Best regards
Anders Jacobsen

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:6rpvt11quctvfjm
kjdhrifcv1u1mibc96e@
4ax.com...
> On Mon, 30 Jan 2006 12:46:06 +0100, Anders wrote:
>
>
> Hi Anders,
>
> The reason is the somewhat wacky way in which a reference in the ORDER
> BY list is used. There are no less than four ways to tell SQL Server how
> to sort the results:
>
> The first two are defined in the ANSI standard:
>
> 1. By ordinal position: "ORDER BY 2" means: order by the 2nd column in
> the SELECT list,
>
> 2. By the alias of an expression in the SELECT list:
> SELECT Column1 AS Yippee, ...
> ...
> ORDER BY Yippee
>
> The last two are non-ANSI-standard syntax extensions:
>
> 3. By an expression: "ORDER BY Column1 * Column2 - Column3".
> The expression cannot reference aliases or ordinal positions of
> columns in the SELECT list; it can, however, reference all columns
> of all tables in the FROM clause.
>
> 4. By column name (this is a special case of #2 if the column name is
> used in the WHERE clause without alias -i.e. the alias is equal to
> the name of the column-, and a special case of #3 otherwise).
>
> Number 4 is where the problems start. For instance, how should this
> query be ordered:
>
> SELECT X as Y, Y as X
> FROM SomeTable
> ORDER BY X
>
> Is X a reference to the column SomeTable.X (aliased as Y in the result)
> or to the alias X (which is assigned to SomeTable.Y)?
>
>
> Of course, if we had used "ORDER BY SomeTable.X", there would have been
> no ambiguity - and that is exactly where SQL Server 2000 displays the
> wacky behaviour I mentioned before. In some casees, SQL Server 2000 will
> completely disregard the prefixed table, use only the column name in the
> ORDER BY clause - and interpret it according to rule #2 above!
>
> (snip)
>
> This is, apparently, such a case. SQL Server disregards dbo.Country, is
> left with only NAME and matches that to the column alias used for
> Clinic.Name.
>
> Some workarounds are:
>
> Change the alias for Clinic.Name:
> [dbo].[CLINIC].[NAME] AS [ClinicName],
>
> Include a dummy operation to force SQL Server to interpret the ORDER BY
> argument as expression:
> ORDER BY [dbo].[COUNTRY].[NAME] + '' DESC
>
> Upgrade to SQL Server 2005 - I've read (somewhere - I've been hunting
> for it but can't find it now) that SQL Server 2005 will always treat a
> qualified name in the ORDER BY clause as a column name.
>
> --
> Hugo Kornelis, SQL Server MVP

Steve Kass

2006-02-05, 3:23 am

Hugo,

This is a bug that has been fixed in 2005. In 2000, aliases are
sometimes completely ignored in the ORDER BY list:

select EmployeeID
from Northwind..Employees
order by Garbage.EmployeeID

This succeeds despite the fact that there is no Garbage alias. The
bug is discreetly acknowledged in the SQL Server 2005 Books Online,
in one of the sections on changes in behavior.

Steve Kass
Drew University

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:6rpvt11quctvfjm
kjdhrifcv1u1mibc96e@
4ax.com...
> On Mon, 30 Jan 2006 12:46:06 +0100, Anders wrote:
>
>
> Hi Anders,
>
> The reason is the somewhat wacky way in which a reference in the ORDER
> BY list is used. There are no less than four ways to tell SQL Server how
> to sort the results:
>
> The first two are defined in the ANSI standard:
>
> 1. By ordinal position: "ORDER BY 2" means: order by the 2nd column in
> the SELECT list,
>
> 2. By the alias of an expression in the SELECT list:
> SELECT Column1 AS Yippee, ...
> ...
> ORDER BY Yippee
>
> The last two are non-ANSI-standard syntax extensions:
>
> 3. By an expression: "ORDER BY Column1 * Column2 - Column3".
> The expression cannot reference aliases or ordinal positions of
> columns in the SELECT list; it can, however, reference all columns
> of all tables in the FROM clause.
>
> 4. By column name (this is a special case of #2 if the column name is
> used in the WHERE clause without alias -i.e. the alias is equal to
> the name of the column-, and a special case of #3 otherwise).
>
> Number 4 is where the problems start. For instance, how should this
> query be ordered:
>
> SELECT X as Y, Y as X
> FROM SomeTable
> ORDER BY X
>
> Is X a reference to the column SomeTable.X (aliased as Y in the result)
> or to the alias X (which is assigned to SomeTable.Y)?
>
>
> Of course, if we had used "ORDER BY SomeTable.X", there would have been
> no ambiguity - and that is exactly where SQL Server 2000 displays the
> wacky behaviour I mentioned before. In some casees, SQL Server 2000 will
> completely disregard the prefixed table, use only the column name in the
> ORDER BY clause - and interpret it according to rule #2 above!
>
> (snip)
>
> This is, apparently, such a case. SQL Server disregards dbo.Country, is
> left with only NAME and matches that to the column alias used for
> Clinic.Name.
>
> Some workarounds are:
>
> Change the alias for Clinic.Name:
> [dbo].[CLINIC].[NAME] AS [ClinicName],
>
> Include a dummy operation to force SQL Server to interpret the ORDER BY
> argument as expression:
> ORDER BY [dbo].[COUNTRY].[NAME] + '' DESC
>
> Upgrade to SQL Server 2005 - I've read (somewhere - I've been hunting
> for it but can't find it now) that SQL Server 2005 will always treat a
> qualified name in the ORDER BY clause as a column name.
>
> --
> Hugo Kornelis, SQL Server MVP

Sponsored Links