[cfaussie] Re: [SQL] Order by on UNION

armstrsj Tue, 18 Feb 2003 21:59:20 -0800

{\rtf1\ansi\ansicpg1252\fromhtml1 \deff0{\fonttbl {\f0\fswiss\fcharset0 Arial;} {\f1\fmodern Courier New;} {\f2\fnil\fcharset2 Symbol;} {\f3\fmodern\fcharset0 Courier New;}} {\colortbl\red0\green0\blue0;\red0\green0\blue255;} \uc1\pard\plain\deftab360 \f0\fs24 {\*\htmltag2 \par } {\*\htmltag242 } {\*\htmltag2 \par } {\*\htmltag18 } {\*\htmltag2 \par } {\*\htmltag34 } {\*\htmltag1 \par } {\*\htmltag1 \par } {\*\htmltag161 } {\*\htmltag1 \par } {\*\htmltag177 } {\*\htmltag241 [cfaussie] Re: [SQL] Order by on UNION} {\*\htmltag185 } {\*\htmltag1 \par } {\*\htmltag41 } {\*\htmltag2 \par } {\*\htmltag50 }\htmlrtf \ltrdoc\ltrpar\ql\ltrch \htmlrtf0 {\*\htmltag0 \par } {\*\htmltag96

}\htmlrtf {\htmlrtf0 {\*\htmltag64}\htmlrtf {\htmlrtf0 Vik,{\*\htmltag72}\htmlrtf\par}\htmlrtf0
{\*\htmltag104

}\htmlrtf }\htmlrtf0 {\*\htmltag0 \par } {\*\htmltag96

}\htmlrtf {\htmlrtf0 {\*\htmltag64}\htmlrtf {\htmlrtf0 {\*\htmltag84  }\htmlrtf \'a0\htmlrtf0 {\*\htmltag72}\htmlrtf\par}\htmlrtf0
{\*\htmltag104

}\htmlrtf }\htmlrtf0 {\*\htmltag0 \par } {\*\htmltag96

}\htmlrtf {\htmlrtf0 {\*\htmltag64}\htmlrtf {\htmlrtf0 Shouldn't you be listening to Lee right now?{\*\htmltag72}\htmlrtf\par}\htmlrtf0
{\*\htmltag104

}\htmlrtf }\htmlrtf0 {\*\htmltag0 \par } {\*\htmltag96

}\htmlrtf \par {\ltrpar\ltrch\ql \htmlrtf0 {\*\htmltag0 \par } {\*\htmltag240 } {\*\htmltag96
}\htmlrtf {\htmlrtf0 {\*\htmltag64}\htmlrtf {\htmlrtf0 \li360 {\*\htmltag148 }\htmlrtf {\fs20 \htmlrtf0 -----Original Message----- {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 }\htmlrtf {\b \htmlrtf0 From: {\*\htmltag92 }\htmlrtf }\htmlrtf0 Viktor Radnai {\*\htmltag4 \par } {\*\htmltag84 }[mailto:[EMAIL PROTECTED]] {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 }\htmlrtf {\b \htmlrtf0 Sent: {\*\htmltag92 }\htmlrtf }\htmlrtf0 Wed 19/02/2003 4:54 {\*\htmltag4 \par } {\*\htmltag84 }PM {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 }\htmlrtf {\b \htmlrtf0 To: {\*\htmltag92 }\htmlrtf }\htmlrtf0 CFAussie Mailing List {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 }\htmlrtf {\b \htmlrtf0 Cc: {\*\htmltag92 }\htmlrtf }\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 }\htmlrtf {\b \htmlrtf0 Subject: {\*\htmltag92 }\htmlrtf }\htmlrtf0 {\*\htmltag4 \par } {\*\htmltag84 }[cfaussie] Re: [SQL] Order by on UNION {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag156 }\htmlrtf }\htmlrtf0 {\*\htmltag72}\htmlrtf\par}\htmlrtf0
{\*\htmltag104
}\htmlrtf }\htmlrtf0 {\*\htmltag0 \par } {\*\htmltag240 } {\*\htmltag64
}\htmlrtf \par \htmlrtf0 \htmlrtf {\htmlrtf0 \li360 {\*\htmltag148 }\htmlrtf {\fs20 \htmlrtf0 Mark, {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 Have you considered using SELECT {\*\htmltag4 \par } {\*\htmltag84 }lower(columnname), columnname FROM ...? {\*\htmltag116
}\htmlrtf \line \htmlrtf0 It's a bit inefficient because it {\*\htmltag4 \par } {\*\htmltag84 }pulls data twice, but it should work {\*\htmltag116
}\htmlrtf \line \htmlrtf0 imho... {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 it's also an untested {\*\htmltag4 \par } {\*\htmltag84 }idea as I'm at MXDU too... {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 Cheers {\*\htmltag116
}\htmlrtf \line \htmlrtf0 Vik {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 --- {\*\htmltag116
}\htmlrtf \line \htmlrtf0 PS: Cheers for {\*\htmltag4 \par } {\*\htmltag84 }the wireless link Geoff {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 Paul - {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 Thanks for the {\*\htmltag4 \par } {\*\htmltag84 }reply, {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 Very similar work around I've got running {\*\htmltag4 \par } {\*\htmltag84 }atm. {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 using lower(columnname) as columnname, and then using css {\*\htmltag4 \par } {\*\htmltag84 }"text-transform : {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 capitalize" to stop it from looking dead {\*\htmltag4 \par } {\*\htmltag84 }ugly. {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 So that DOES work... just not ideal. {\*\htmltag84  }\htmlrtf \'a0\htmlrtf0 Was trying to {\*\htmltag4 \par } {\*\htmltag84 }stick with the original {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 formatting, if at all {\*\htmltag4 \par } {\*\htmltag84 }possible. {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 Cheers, {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag4 \par } {\*\htmltag84 }Mark {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 [EMAIL PROTECTED] {\*\htmltag4 \par } {\*\htmltag84 } {\*\htmltag84 <}\htmlrtf <\htmlrtf0 [EMAIL PROTECTED] {\*\htmltag84 >}\htmlrtf >\htmlrtf0 wrote: {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 On Wed, Feb 19, {\*\htmltag4 \par } {\*\htmltag84 }2003 at 12:37:26PM +1100, Mark M wrote: {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 Having {\*\htmltag4 \par } {\*\htmltag84 }issues on a order by with a union {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 query looks something like {\*\htmltag4 \par } {\*\htmltag84 }this: {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 -- {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 select * {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 from {\*\htmltag4 \par } {\*\htmltag84 }tableOne {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 UNION {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 select * {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag4 \par } {\*\htmltag84 }from tableTwo {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 Order By tableColumn ASC {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag4 \par } {\*\htmltag84 } {\*\htmltag84 >}\htmlrtf >\htmlrtf0 Untested, but try: {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 select upper(tablecolumn) as {\*\htmltag4 \par } {\*\htmltag84 }upfield, * {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 from tableone {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 UNION {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 select {\*\htmltag4 \par } {\*\htmltag84 }upper(tablecolumn) as upfield, * {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 from tabletwo {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 order {\*\htmltag4 \par } {\*\htmltag84 }by upfield asc {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 What's happening here is that you're {\*\htmltag4 \par } {\*\htmltag84 }extracting the upper {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 cased field as a new field, then ordering on {\*\htmltag4 \par } {\*\htmltag84 }that new field. {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 You might find you have to refer to {\*\htmltag4 \par } {\*\htmltag84 }the field by number, {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 rather than name. eg: {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag4 \par } {\*\htmltag84 } {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84  }\htmlrtf \'a0\htmlrtf0 {\*\htmltag84  }\htmlrtf \'a0\htmlrtf0 {\*\htmltag84  }\htmlrtf \'a0\htmlrtf0 order by 1 asc {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 Cheers from {\*\htmltag4 \par } {\*\htmltag84 }MXDU {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 Paul Haddon {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 Technical Services {\*\htmltag4 \par } {\*\htmltag84 }Manager {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 Formstar Print Technologies {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag4 \par } {\*\htmltag84 } {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 --- {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 You are currently subscribed {\*\htmltag4 \par } {\*\htmltag84 }to cfaussie as: [EMAIL PROTECTED] {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 To unsubscribe send a {\*\htmltag4 \par } {\*\htmltag84 }blank email to {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 [EMAIL PROTECTED] {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag4 \par } {\*\htmltag84 } {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 MX Downunder AsiaPac DevCon - {\*\htmltag84 }\htmlrtf {\field{\*\fldinst{HYPERLINK "http://mxdu.com/"}}{\fldrslt\cf1\ul \htmlrtf0 http://mxdu.com/\htmlrtf }\htmlrtf0 \htmlrtf }\htmlrtf0 {\*\htmltag92 } {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 {\*\htmltag4 \par } {\*\htmltag84 }-- {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 ICQ: 3094740 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag84 >}\htmlrtf >\htmlrtf0 [EMAIL PROTECTED] {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 --- {\*\htmltag116
}\htmlrtf \line \htmlrtf0 You are {\*\htmltag4 \par } {\*\htmltag84 }currently subscribed to cfaussie as: [EMAIL PROTECTED] {\*\htmltag116
}\htmlrtf \line \htmlrtf0 To unsubscribe {\*\htmltag4 \par } {\*\htmltag84 }send a blank email to [EMAIL PROTECTED] {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag116
}\htmlrtf \line \htmlrtf0 MX {\*\htmltag4 \par } {\*\htmltag84 }Downunder AsiaPac DevCon - {\*\htmltag84 }\htmlrtf {\field{\*\fldinst{HYPERLINK "http://mxdu.com/"}}{\fldrslt\cf1\ul \htmlrtf0 http://mxdu.com/\htmlrtf }\htmlrtf0 \htmlrtf }\htmlrtf0 {\*\htmltag92 } {\*\htmltag116
}\htmlrtf \line \htmlrtf0 {\*\htmltag156 }\htmlrtf }\htmlrtf0 \htmlrtf\par}\htmlrtf0 \htmlrtf \par \htmlrtf0 {\*\htmltag72
} {\*\htmltag104

}\htmlrtf }\htmlrtf0 {\*\htmltag0 \par } {\*\htmltag0 \par } {\*\htmltag58 } {\*\htmltag2 \par } {\*\htmltag27 }}

[cfaussie] Re: [SQL] Order by on UNION

Reply via email to