SEO

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





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

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

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
> 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.net> wrote in message
news:1166906010.557020.127250@79g2000cws.googlegro ups.com...
>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/Fo...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:
>>> 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/Fo...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:
> 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.

>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/Fo...Forum2006.html