Posted by Ed Murphy on 12/23/06 03:18

Sharif Islam wrote:

>> "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"

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

> 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.

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,'')

[Back to original message]