re arrrange nulls in order by clause

mat41 · August 2nd, 2007, 02:30 AM

Hello SQL people

Null values are treated as the lowest possible value in recordsets. is there some easy way to make the NULLs appear last without using DESC?

Why you may ask, mmmm...

The status value which I am ordering by is an integer and the NULLS are 0's (due to my relationships) therefore my results are (the following represents 17 records ):
0,0,0,1,1,1,2,2,2,2,3,3,3,4,5,6,7
I wish to have an output of:
1,1,1,2,2,2,2,3,3,3,4,5,6,7,0,0,0

TYIA

Wind is your friend
Matt

gbianchi · August 2nd, 2007, 07:51 AM

hey there.. can you just exclude the null values from the first query and UNION that query with one that show only the null values??? just an idea...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

mat41 · August 2nd, 2007, 06:44 PM

Thankyou for your time however thats not my objective. The status value is on of a dozen fields in the record sets row, its NULL but other records are not. My question remains...

TYIA

Wind is your friend
Matt

mat41 · August 2nd, 2007, 06:52 PM

To further explain:

>get record set
>render on page
>allow the user to filter colums by clicking on the column headings
>one of these headings is status (this is an integer data type)
>any record waiting administration has a status of 0. The statusID in the main table links to a status table. Status table looks like:

ID statDesc
0 to be defined
1 Open
2 Closed
3 Action pending

So I wish for the 0 status values to be ordered last EG:
1,1,1,2,2,2,2,3,3,3,4,5,6,7,0,0,0
note: these numbers above represent the individual status values in a record set holding 17 rows

TYIA

Wind is your friend
Matt

dparsons · August 2nd, 2007, 09:33 PM

Since 0 is an arbitrary value anyway, instead of assigning the value 0 to null status values, why not assign the null values a value that is higher then your highest status code? In the above example, instead of 0, assign it the value of 8.

Just a thought.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========

mat41 · August 5th, 2007, 07:53 PM

Yes a thought I wish has crossed my mind when building the tables a while back. I suspected there was no such order by function or the like.

Thanking you...

Wind is your friend
Matt

Jonax · August 6th, 2007, 04:16 AM

Maybe something like this:

Code:

SELECT [ID]
      ,[Something]
      ,ISNULL([StatID],0)
FROM [tbWrox63588]
ORDER BY
      ISNULL(CAST([StatID] AS FLOAT)/10,1)

mat41 · August 6th, 2007, 07:46 PM

Thanks for your input but no nulls are present in the table. mmmm looks like the title of my post was not well thought out.

No nulls are allowed in the statsID field. A default value of 0 is inserted which as described in my post, four above this one.

Wind is your friend
Matt

Jeff Mason · August 6th, 2007, 08:40 PM

How about using a CASE expression in the ORDER BY clause? You'll need to define a value to use when your StatID column has a zero value. This should be a maximum which will be larger than all other legal values (so it sorts last):

SELECT *
FROM yourtable
ORDER BY CASE WHEN StatID = 0 THEN 9999 ELSE StatID END;

should do the trick.

Note that some implementations of SQL (this is the language forum after all) require that columns in the ORDER BY clause be present in the SELECT list. If this is true for your database, just define a column which is the CASE expression, alias it, and ORDER BY that.

Jeff Mason
[email protected]

mat41 · August 6th, 2007, 09:20 PM

hi Jeff

Here is my very conditional (the make up based on user filters) sql written to the browser:

SELECT DISTINCT osirHazards.id,osirHazards.uniqueHName,osirHazards .hName,osirHazards.hri,
osirHazards.status,osirHazActionOrganisation.oName ,osirHazards.reviewdate,osirhazards.actEmailDate,
osirHazards.risk,osirHazActionOrganisation.forder FROM (((osirHazards
LEFT OUTER JOIN osirhazActionOrganisation ON osirhazActionOrganisation.oid = osirHazards.organisationID)
LEFT OUTER JOIN hazControlLevels ON osirHazards.id = hazControlLevels.hazID)
LEFT OUTER JOIN controlLevels ON hazControlLevels.controlID = controlLevels.ID)
WHERE (osirHazards.inactive=0) AND (osirHazards.id > 0)
AND (osirHazards.hri >= 0 AND osirHazards.hri <= 20)
ORDER BY osirHazards.status

If I apply the CASE syntax you suggest it tells me my order by field is not in the select list. Am I missing something - 'osirHazards.status' certainly is in the select list. I am simply chnaging my order by line to:

ORDER BY CASE WHEN osirHazards.status = 0 THEN 9999 ELSE osirHazards.status END;

If I remove the DISTINCT and change the order by line to your suggestion the query works as you explain and how I wish it to.

TYIA

Wind is your friend
Matt