Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 2nd, 2007, 02:30 AM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default re arrrange nulls in order by clause

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
__________________
Wind is your friend
Matt
  #2 (permalink)  
Old August 2nd, 2007, 07:51 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========
  #3 (permalink)  
Old August 2nd, 2007, 06:44 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
  #4 (permalink)  
Old August 2nd, 2007, 06:52 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
  #5 (permalink)  
Old August 2nd, 2007, 09:33 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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

================================================== =========
  #6 (permalink)  
Old August 5th, 2007, 07:53 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
  #7 (permalink)  
Old August 6th, 2007, 04:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 184
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Jonax
Default

Maybe something like this:
Code:
SELECT [ID]
      ,[Something]
      ,ISNULL([StatID],0)
FROM [tbWrox63588]
ORDER BY
      ISNULL(CAST([StatID] AS FLOAT)/10,1)
  #8 (permalink)  
Old August 6th, 2007, 07:46 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
  #9 (permalink)  
Old August 6th, 2007, 08:40 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
je.mason@comcast.net
  #10 (permalink)  
Old August 6th, 2007, 09:20 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
IN and NULLs joxa83 SQL Server 2005 8 September 29th, 2008 03:03 AM
Order by clause priyan.viji SQL Language 2 December 17th, 2007 02:45 AM
WHERE Clause, LIKE, and Nulls jurio SQL Language 5 May 28th, 2007 02:53 AM
order by In clause items? cs8271506 SQL Language 2 September 19th, 2004 08:13 PM
Order by clause mateenmohd SQL Server 2000 4 April 6th, 2004 06:48 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.