Re: OT: SQL Question -- Order by a column's value?

2008-01-26 Thread Brian Kotek
I actually prefer to do this in the ORDER BY clause (keeping the ordering
logic in the ORDER BY instead of in the SELECT) but the end result is the
same. If you won't or can't add a sort column to the table, a CASE statement
is about the only other way to do this in the query itself.

On Jan 25, 2008 3:00 PM, Ian Skinner [EMAIL PROTECTED] wrote:

 Che Vilnonis wrote:
  I was trying to do that w/o adding another column. Can it be done?
 Yes, see Crow's, Charlie's or my post on using CASE to create an inline
 sort column with SQL.



 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297531
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Charlie Griefer
something like...

SELECT
 Colleges,
 CASE
  when Colleges = 'Harvard' THEN 1
  when Colleges = 'Princeton' THEN 2
  when Colleges = 'Dartmouth' THEN 3
 END AS collegeOrder
FROM
 myTable
ORDER BY
 collegeOrder

(not tested) :)

On Jan 25, 2008 10:41 AM, Che Vilnonis [EMAIL PROTECTED] wrote:
 Suppose I have a small set of data with a column named Colleges. Is there
 a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che


 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297453
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


RE: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Che Vilnonis
I was trying to do that w/o adding another column. Can it be done? 

-Original Message-
From: Todd [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 25, 2008 1:50 PM
To: CF-Talk
Subject: Re: OT: SQL Question -- Order by a column's value?

Nope, add a sort_order column and sort your colleges appropriately.

On Jan 25, 2008 1:41 PM, Che Vilnonis [EMAIL PROTECTED] wrote:

 Suppose I have a small set of data with a column named Colleges. Is 
 there a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che




~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297456
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Ian Skinner
There is no way to do it with pure SQL alone.

Dominic

Well actually you can do in pure SQL.  SQL has code that can be used to create 
dynamic columns and values on the fly in your record set and then one can order 
on this set.  It sort of depends on whether the desire order is permanent or 
flexible.  I.E. sometimes one wants 'Harvard', 'Princeton' and 'Dartmouth' and 
another time one wants 'Princeton', 'Dartmouth' and 'Harvard'.


SELECT
  CASE college
 WHEN 'Harvard' THEN 1
 WHEN 'Princeton' THEN 2
 WHEN 'Dartmouth' THEN 3
  END AS sortCol

FROM aTable

ORDER BY sortCol

The exact syntax can very from database to database management system.




~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297460
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Ian Skinner
Che Vilnonis wrote:
 I was trying to do that w/o adding another column. Can it be done? 
Yes, see Crow's, Charlie's or my post on using CASE to create an inline 
sort column with SQL.



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297467
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Todd
Sorry, add sort_order column and then do an ORDER BY sort_order and set all
the colleges in the appropriate sorting that you want it to be.

On Jan 25, 2008 1:49 PM, Todd [EMAIL PROTECTED] wrote:


 Nope, add a sort_order column and sort your colleges appropriately.


 On Jan 25, 2008 1:41 PM, Che Vilnonis [EMAIL PROTECTED] wrote:

  Suppose I have a small set of data with a column named Colleges. Is
  there
  a way to write an ORDER BY statement to say something like...
  ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges
  'Dartmouth'???
 
  Just wondering... Che


http://www.web-rat.com/


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297451
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Todd
Nope, add a sort_order column and sort your colleges appropriately.

On Jan 25, 2008 1:41 PM, Che Vilnonis [EMAIL PROTECTED] wrote:

 Suppose I have a small set of data with a column named Colleges. Is
 there
 a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297450
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


RE: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Dawson, Michael
Although you can do it as Charlie demonstrated, using CASE, Dominic's
solution is probably the best.

Put the data in a table where it belongs.  Then, that same data can be
reused for other purposes.

M!ke

-Original Message-
From: Dominic Watson [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 25, 2008 12:55 PM
To: CF-Talk
Subject: Re: OT: SQL Question -- Order by a column's value?

Do you mean put them in a predifind order based on the college, other
than alphabetical?

If so, and if you have a lookup table for your colleges, you will have
to add a numerical column called 'Ordinal' (or something else) with
which you can set their order. Then simply order by that in your SQL
statement. There is no way to do it with pure SQL alone.

Dominic


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297461
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Crow T. Robot
Yes, you can do this using case statements in your order by:

example:

select * from viewoffers where [EMAIL PROTECTED]

order by

case status

when 'active' then 1

when 'rejected' then 2

else 99

end



Of course, this is really a kludge.  The DB should be deisgned a little
better, but sometimes a kludge is what you need to get the job done. Not
everything can be elegant.

On Jan 25, 2008 12:41 PM, Che Vilnonis [EMAIL PROTECTED] wrote:

 Suppose I have a small set of data with a column named Colleges. Is
 there
 a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che


 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297459
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4


Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Ian Skinner
Che Vilnonis wrote:
 Suppose I have a small set of data with a column named Colleges. Is there
 a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che
If  I understand your question correctly, you want to order a record set 
by first 'Harvard' record(s) then 'Princeton' record(s) and finally 
'Dartmouth' record(s).  If so yes, but you do most of the work in the 
SELECT clause.  Using logic functions in the SELECT clause such as CASE 
you can create dynamically create a sortable column with the appropriate 
values such as Harvard = 1, Princeton = 2 and Dartmouth = 3.  Then you 
just ORDER BY on this dynamic column and you the the result set you desire.

HTH
Because I don't have an example at my finger tips or remember the exact 
syntax off the top of my head to cobble one together this exact second.

Ian


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297452
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


Re: OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Dominic Watson
Do you mean put them in a predifind order based on the college, other than
alphabetical?

If so, and if you have a lookup table for your colleges, you will have to
add a numerical column called 'Ordinal' (or something else) with which you
can set their order. Then simply order by that in your SQL statement. There
is no way to do it with pure SQL alone.

Dominic


On 25/01/2008, Che Vilnonis [EMAIL PROTECTED] wrote:

 Suppose I have a small set of data with a column named Colleges. Is
 there
 a way to write an ORDER BY statement to say something like...
 ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

 Just wondering... Che


 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297454
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4


OT: SQL Question -- Order by a column's value?

2008-01-25 Thread Che Vilnonis
Suppose I have a small set of data with a column named Colleges. Is there
a way to write an ORDER BY statement to say something like...
ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???

Just wondering... Che


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297449
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4