Re: OT: SQL Question -- Order by a column's value?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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
