Re: Order By [blank]

I haven't tried the union method.... the query i have is actually quite a bit more complicated than just a simple select * from a single table, so while it may work, it might take a while to write it if I am reading all this right. But yes, the ifnull() method works just fine. Thanks for your help. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 On Dec 21, 2006, at 10:12 AM, <tg-php@gryffyndevelopment.com> <tg- php@gryffyndevelopment.com> wrote: > You shouldn't have to do that. the IFNULL() handles all that. If > the item is null, it returns an emptry string ''. If it's blank/ > empty, it returns an empty string. This is just used for the > comparison = ''. This determines if it's empty or null and if so, > returns 'ZZZZZZZZZZZZZZZZZZZZZZZZZZ', if not, it returns the > unaltered value. And again, this returned value is only used for > the sorting. The values you get from "select *" will be unaltered. > > Is this more efficient than doing two SELECTs and a UNION? I have > no idea. But I like to keep things as clean as possible and in my > reading and experience, letting the server handle an IFNULL() > function should be quicker than doing four value checks (is null, > is not null, = '' and != ''), collecting the values of two SELECTS > then checking to see if it's able to UNION them together. > > Also, less code/typing typically means less chance of typos. > > > BTW: In my example, realistically you could probably shorten the > morphed value to "ZZ" or "ZZZ" unless you think you'll values in > your database that will start with "ZZZZ" and get bumped lower on > the sorting. > > -TG > > = = = Original message = = = > > In case the blank is a null or is really a blank: > > select * from blank where tchar_10 is not null and tchar_10 != '' > union all > select * from blank where tchar_10 is null or tchar_10 = '' > > - Dave > > On 12/21/06, tg-php@gryffyndevelopment.com <tg- > php@gryffyndevelopment.com> > wrote: >> >> This is a little weird looking, but should do the job. Remember that >> items in your 'order by' can be manipulated conditionally. In >> this case, >> I'm looking for NULL as well as '' (empty) and changing it to >> something that >> should come after all your normal alphabetical values, but it >> doesn't change >> what appears in your results. This only affects the sorting: >> >> select * from sometable order by if(ifnull(somecolumn, '') = '', >> 'ZZZZZZZZZZZZZZZZZZZZZZZZ', somecolumn) >> >> Hope that helps. >> >> -TG >> >> = = = Original message = = = >> >> I have this column in mysql: >> >> A >> F >> D >> [ empty ] >> A >> C >> >> If I do an order by on that column, this is what I get: >> >> [ empty ] >> A >> A >> C >> D >> F >> >> What I would like is this: >> >> A >> A >> C >> D >> F >> [ empty ] >> >> Is there any way to achieve this in a single MySQL query? Using DESC >> in this case doesn't work, because while it puts the empty row in the >> last place, it does the rest as well. I could also do 2 queries where >> it calls it once in order WHERE !='', and then do another query to >> get the empty ones, but that seems a bit cumbersome. >> >> -- >> Kevin Murphy >> Webmaster: Information and Marketing Services >> Western Nevada Community College >> www.wncc.edu >> 775-445-3326 >> >> >> >> >> -- >> Kevin Murphy >> Webmaster: Information and Marketing Services >> Western Nevada Community College >> www.wncc.edu >> 775-445-3326 > > > ___________________________________________________________ > Sent by ePrompter, the premier email notification software. > Free download at http://www.ePrompter.com. > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >