Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
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
 
Old August 7th, 2007, 05:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
je.mason@comcast.net
 
Old August 7th, 2007, 06:52 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
 
Old August 7th, 2007, 08:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
je.mason@comcast.net
 
Old August 7th, 2007, 08:10 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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





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.