ORDER BY CASE

this is my query=

"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"

My SortKey can be NULL. Here's the output I am getting:
(the || is to denote sortkey column)

37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||
61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|

I want use Sortkey when it is not null. desired output:

61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|
37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||Sharif Islam wrote:

Quote:

Originally Posted by

this is my query=
>
"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"

ok I think figured it out, i needed a desc at the end.

SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end desc"

let me know if there's a better way to do this.

Quote:

Originally Posted by

>
My SortKey can be NULL. Here's the output I am getting:
(the || is to denote sortkey column)
>
37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||
61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|
>
I want use Sortkey when it is not null. desired output:
>
61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|
37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||

|||Sharif Islam wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
>(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
>type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"

Quote:

Originally Posted by

Quote:

Originally Posted by

>I want use Sortkey when it is not null. desired output:

Quote:

Originally Posted by

ok I think figured it out, i needed a desc at the end.

Quote:

Originally Posted by

SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' AND
type='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end desc"

Quote:

Originally Posted by

let me know if there's a better way to do this.

This ensures that the query will return rows with non-null SortKey
first, rows with null SortKey second.

It does /not/ ensure that the query will return rows with SortKey
"Africa" first, rows with SortKey "Brazil" second. It happened
to work that way this time, but there are no guarantees that it
will work that way every time. To get that guarantee, do this:

order by
CASE WHEN SortKey is not null then 1 else 2 end,
coalesce(SortKey,'')|||A quick lesson in good software engineering and SQL: Put the sort key
into a column in your result SELECT list and give it a name.

1) Good SQL: Using an expression in an ORDER BY clause is a
proprietary "feature" in SQL Server and not Standard SQL. The ORDER BY
caluse is alawyas part of a cursor, not a SELECT statement, since
DSELECT produces a table which has no order by definition.

2) Good S.E.:Always show what you used to sort a result set to the next
tier of the architecture. They might need to use it. Hey, the final
user might fidn data easier to find on his display if it was there!|||>A quick lesson in good software engineering and SQL: Put the sort key

Quote:

Originally Posted by

into a column in your result SELECT list and give it a name.
>
1) Good SQL: Using an expression in an ORDER BY clause is a
proprietary "feature" in SQL Server and not Standard SQL. The ORDER BY
caluse is alawyas part of a cursor, not a SELECT statement, since
DSELECT produces a table which has no order by definition.
>
2) Good S.E.:Always show what you used to sort a result set to the next
tier of the architecture. They might need to use it. Hey, the final
user might fidn data easier to find on his display if it was there!
>

That is a mistake class room coders often make; they don't take into
consideration the extra resource the extra columns require when say
bandwidth may be a limiting factor to scalability.

Why pass something you don't need?

This is a MICROSOFT SQL SERVER news group (you may do well to remember that)
and NOT a STANDARD SQL one - as far as I know no such group exists which
suggests there is not much call for it.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1166906010.557020.127250@.79g2000cws.googlegro ups.com...

Quote:

Originally Posted by

>A quick lesson in good software engineering and SQL: Put the sort key
into a column in your result SELECT list and give it a name.
>
1) Good SQL: Using an expression in an ORDER BY clause is a
proprietary "feature" in SQL Server and not Standard SQL. The ORDER BY
caluse is alawyas part of a cursor, not a SELECT statement, since
DSELECT produces a table which has no order by definition.
>
2) Good S.E.:Always show what you used to sort a result set to the next
tier of the architecture. They might need to use it. Hey, the final
user might fidn data easier to find on his display if it was there!
>

|||Joe,

How do you answer the question:
Give me all employees of the 5 best paid managers?
This query is very easy to write with a nested TOP/ORDER BY.

SELECT * FROM EMP
WHERE MGRID IN (SELECT TOP 5 EMPID FROM MGR
ORDER BY SALARY DESC)

It gets really nasty without. Matter of fact I can't think of a way
without cheating (e.g. using ROW_NUMBER() to sneak in the ORDER BY
through the back door).

Are you saying SQL shouldn't be able to answer such queries without
escaping into the application? I doubt that's what Codd had in mind...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||>Are you saying SQL shouldn't be able to answer such queries without escaping into the application? <<

I would use the "ROW_NUMBER() OVER ()" that you guys at IBM put into
DB2 !!|||--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Are you saying SQL shouldn't be able to answer such queries without escaping into the application? <<

>
I would use the "ROW_NUMBER() OVER ()" that you guys at IBM put into
DB2 !!

... and the SQL Standard...

Well, that's what I referred to as cheating, since it requires usage
ROW_NUMBER() OVER ( _ORDER_BY_ ...). So you avoid one ORDER BY by
adding another. How come one is bad while the other isn't ?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||>So you avoid one ORDER BY by adding another. How come one is bad while the other isn't ? <<

Language Standards. And an underlying definition based on von Nueman's
definition of ordinal numbers in Set Theory. DUH! :)|||--CELKO-- wrote:

Quote:

Originally Posted by

Language Standards.

Winds of change are blowing and they wisper "FETCH FIRST n ROWS". Every
SQL based product has some sort of mechanism to get the job done.
Doesn't that just scream need? Whether it's TOP, ROWNUM, LIMIT OFFSET...
All of them address the need to cut pieces out of a set based on order.

Quote:

Originally Posted by

>And an underlying definition based on von Nueman's
definition of ordinal numbers in Set Theory. DUH! :)

Living in the past, are we...? The result of a refusal to acknowledge a
need is that the standard got fractured at this point.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||On Sat, 23 Dec 2006 18:17:54 -0500, Serge Rielau <srielau@.ca.ibm.com>
wrote:

Quote:

Originally Posted by

>Give me all employees of the 5 best paid managers?
>This query is very easy to write with a nested TOP/ORDER BY.
>
>SELECT * FROM EMP
WHERE MGRID IN (SELECT TOP 5 EMPID FROM MGR
ORDER BY SALARY DESC)
>
>It gets really nasty without. Matter of fact I can't think of a way
>without cheating (e.g. using ROW_NUMBER() to sneak in the ORDER BY
>through the back door).

If you allow WITH DUPS instead of arbitrarily dropping one:

SELECT *
FROM EMP
WHERE MGRID IN
(SELECT EMPID FROM MGR as M1
WHERE (SELECT count(*) from MGR as M2
WHERE M1.SALARY <= M2.SALARY) <= 5)

Roy Harvey
Beacon Falls, CT