re arrrange nulls in order by clause

Jeff Mason · August 7th, 2007, 05:38 AM

Oh yeah. Most implementations require that the columns in the ORDER BY clause be present in the SELECT list for a SELECT DISTINCT. It's because this is actually a grouping operation. Your particular SQL implementation may also require it. (I think the argument goes that you shouldn't present something in an order that cannot be verified. If an ORDER BY value is not in the SELECT list, you can't tell if the order is correct...)

Anyway, you can just add the CASE expression to your SELECT list, name it as an alias, and ORDER BY that:

Code:

SELECT DISTINCT osirHazards.id, .... ,
                CASE WHEN osirHazards.status = 0 THEN 9999 ELSE osirHazards.status END AS Sortorder
  FROM ...
ORDER BY Sortorder

(why are you using a SELECT DISTINCT in this query - can there really be duplicates?)

Uh-oh - I see that you want to present this data in a grid on a page so the user can click on this status column to sort by it. This may be trickier than it looks - to get the underlying column to sort correctly yet still display it's actual value.

Your best (and certainly simplest) solution may have already been posted - change the status values so they naturally sort the way you want.

Jeff Mason
[email protected]

mat41 · August 7th, 2007, 06:52 PM

Interesting.... I still dont understand why its saying my order by field is not in the select list. mmmmm, anyhow not to worry my ASP is a standard where I can make the record set show what i want from it. Clearly my SQL skills need a bit of sharpening.

Im sure there is alot more I can do with SQL. I tend to make the server work harder when looping through record sets manipulating them rather than getting better at more complex satements etc. Instead of trying to make this page work a little better using SQL, I will go with it and be sure to come back here in the future.

Thankyou all for your time :0)

Wind is your friend
Matt

Jeff Mason · August 7th, 2007, 08:06 PM

It says your ORDER BY field is not in the SELECT list, because, er, your ORDER BY field is not in the SELECT list. :D

You are not ordering by a particular column, but rather by the value of an expression (the CASE expression), and that's not in the list.

Jeff Mason
[email protected]

mat41 · August 7th, 2007, 08:10 PM

I see the case expression is not in the list (the tblename.fldName certainly is)

Thanks for your time and have a good day...

Wind is your friend
Matt