Boolean Sort in SQL Server

  • Thread starter Thread starter David W. Fenton
  • Start date Start date
D

David W. Fenton

I'm working on an upsizing project and the existing Access app uses
this SQL (and works fine):

SELECT tblDonor.DonorID
FROM tblDonor
WHERE tblDonor.LNAME="Weingartner"
AND tblDonor.FNAME Like "Jason*"
AND tblDonor.DonorID<>219784
ORDER BY tblDonor.LNAME="Weingartner",
tblDonor.SXLName2="W84968",
tblDonor.FNAME="Jason S.",
tblDonor.DonorID

When I run this against the SQL Server back end, it chokes.

If I remove the ORDER BY, it runs fine, but I need the ORDER BY for
presentation.

Is there a TSQL method of ordering by the Boolean result of a
comparison?

Or will I have to do the sort some other way?

Experimenting with this I tried creating a disconnected recordset,
but couldn't find a way to apply an ORDER BY once I'd populated it.
Is that possible?
 
I think you have to have the boolean expression as a part of the select
(ANSI compliancy, cannot order by w/o it being part of the SELECT statement)
At least you do in Oracle.
MSSQL, Oracle & Jet do however support the non ANSI Order By Column Number

ie
SELECT NAME, CBOOL(Name='Jason') As Sort
FROM MyTable
Order by 2,1

is perfectly valid

Since it's you (and I'm glad you've resolved the import problems)
I won't say that a select .. case statement is the best way to make that
boolean expression <g>

HTH

Pieter
 
To elaborate further
Both MSSQL & Oracle don't apply the Order By untill the data has been
gathered & in fact have "lost track" of the originating query
which is why I suppose ORDER BY <column number> works

Pieter

this time in the role of a Oracle Professional DBA

"Pieter Wijnen"
I think you have to have the boolean expression as a part of the select
(ANSI compliancy, cannot order by w/o it being part of the SELECT
statement)
At least you do in Oracle.
MSSQL, Oracle & Jet do however support the non ANSI Order By Column Number

ie
SELECT NAME, CBOOL(Name='Jason') As Sort
FROM MyTable
Order by 2,1

is perfectly valid

Since it's you (and I'm glad you've resolved the import problems)
I won't say that a select .. case statement is the best way to make that
boolean expression <g>

HTH

Pieter
Click to expand...
 
Hi David ,

T-SQL method
----
SELECT tblDonor.DonorID
FROM tblDonor
WHERE tblDonor.LNAME="Weingartner"
AND tblDonor.FNAME Like "Jason*"
AND tblDonor.DonorID<>219784
ORDER BY
CASE WHEN tblDonor.LNAME="Weingartner" THEN 0 ELSE 1 END,
CASE WHEN tblDonor.SXLName2="W84968" THEN 0 ELSE 1 END,
CASE WHEN tblDonor.FNAME="Jason S." THEN 0 ELSE 1 END,
tblDonor.DonorID
 
"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.
replace.with.norway> wrote in
I think you have to have the boolean expression as a part of the
select (ANSI compliancy, cannot order by w/o it being part of the
SELECT statement) At least you do in Oracle.
MSSQL, Oracle & Jet do however support the non ANSI Order By
Column Number

ie
SELECT NAME, CBOOL(Name='Jason') As Sort
FROM MyTable
Order by 2,1
Click to expand...

As it turns out, putting the CBOOL() on the expression took care of
it. Apparently without it, Jet was sending the whole thing over to
SQL Server to process, and it didn't know what to do. But with the
VBA expression wrapped around it, it apparently retrieved the data
and then did all the sorting locally (which is perfectly fine,
actually, as the rows would already have been restricted).
 
T-SQL method
----
SELECT tblDonor.DonorID
FROM tblDonor
WHERE tblDonor.LNAME="Weingartner"
AND tblDonor.FNAME Like "Jason*"
AND tblDonor.DonorID<>219784
ORDER BY
CASE WHEN tblDonor.LNAME="Weingartner" THEN 0 ELSE 1 END,
CASE WHEN tblDonor.SXLName2="W84968" THEN 0 ELSE 1 END,
CASE WHEN tblDonor.FNAME="Jason S." THEN 0 ELSE 1 END,
tblDonor.DonorID
----
Click to expand...

This did not work for me as I got the message that CASE WHEN didn't
work. I am using SQL Server 2000, so maybe that makes a difference.

In any event, I was able to get Jet and SQL Server to work together
in harmony with the ORDER BY simply by putting VBA's CBOOL() around
the expressions, so I'm a happy camper.

Thanks to all for their help.
 
David W. Fenton said:
This did not work for me as I got the message that CASE WHEN didn't
work. I am using SQL Server 2000, so maybe that makes a difference.
Click to expand...

Case When is T-SQL 2000 function. It Works.
In any event, I was able to get Jet and SQL Server to work together
in harmony with the ORDER BY simply by putting VBA's CBOOL() around
the expressions, so I'm a happy camper.
Click to expand...

CBOOL() is not T-SQL Function.
You ask a T-SQL statement
--------
But you don't need a T-SQL statement.

I think you need a Jet statement.
----
SELECT ....
FROM ...
ORDER BY
Iif(tblDonor.LNAME="Weingartner",0,1),
Iif(tblDonor.SXLName2="W84968",0,1),
Iif(tblDonor.FNAME="Jason S.",0,1),
tblDonor.DonorID
 
Not surprised by that <g>.
I was just under the impression that you wanted a T-SQL (PassThrough Query)
The Case When Would have to go as part of the select for the reasons I
stated earlier

Pieter
 
"David W. Fenton" <[email protected]> ha scritto nel
messaggio


Case When is T-SQL 2000 function. It Works.
Click to expand...

I tried creating a view to run the SQL you provided, and it would
not parse.
CBOOL() is not T-SQL Function.
You ask a T-SQL statement
Click to expand...

I asked how to get it done, thinking that doing it in TSQL was the
only way. Turns out, the easiest way was to use CBOOL(), as it
doesn't require me to create a view or use a passthrough query.
----
----
But you don't need a T-SQL statement.

I think you need a Jet statement.
----
SELECT ....
FROM ...
ORDER BY
Iif(tblDonor.LNAME="Weingartner",0,1),
Iif(tblDonor.SXLName2="W84968",0,1),
Iif(tblDonor.FNAME="Jason S.",0,1),
tblDonor.DonorID
----
Click to expand...

CBOOL() does this without needing to supply the values.

That is, now the results with a SQL Server back end are exactly the
same as they were with a Jet back end (using 0 and 1 for Boolean
values would require reversing the sort order).
 
Back
Top