Order By [blank]

--Apple-Mail-3--564223130
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

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



--Apple-Mail-3--564223130--

RE: Order By [blank]

Depending on your MySQL version you could use a subquery by combining the
two queries you mentioned, for a fairly straight-forward query.

http://dev.mysql.com/tech-resources/articles/4.1/subqueries. html
http://mysqld.active-venture.com/Subqueries.html

You could read about optimizing subqueries for optimum queries.

-----Original Message-----
From: Kevin Murphy [mailto:php@stubborndonkey.com]
Sent: Thursday, December 21, 2006 10:49 PM
To: php-db@lists.php.net
Subject: [PHP-DB] Order By [blank]

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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Order By [blank]

--Apple-Mail-6--562985758
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

Unfortunately, I'm on 4.0.x so sub-queries are out. And yeah, I
should get my host to upgrade.... but we both work for the government
so that isn't happening. ;-)

Any other thoughts.

--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada Community College
www.wncc.edu
775-445-3326


On Dec 21, 2006, at 9:30 AM, Naintara wrote:

> Depending on your MySQL version you could use a subquery by
> combining the
> two queries you mentioned, for a fairly straight-forward query.
>
> http://dev.mysql.com/tech-resources/articles/4.1/subqueries. html
> http://mysqld.active-venture.com/Subqueries.html
>
> You could read about optimizing subqueries for optimum queries.
>
> -----Original Message-----
> From: Kevin Murphy [mailto:php@stubborndonkey.com]
> Sent: Thursday, December 21, 2006 10:49 PM
> To: php-db@lists.php.net
> Subject: [PHP-DB] Order By [blank]
>
> 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
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


--Apple-Mail-6--562985758--

Re: Order By [blank]

------=_Part_59542_8010391.1166723302291
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

What about a union? Does mySql 4.0.x support it?

select * from blank where tchar_10 != ''
union all
select * from blank where tchar_10 = ''


- Dave



On 12/21/06, Naintara wrote:
>
> Depending on your MySQL version you could use a subquery by combining the
> two queries you mentioned, for a fairly straight-forward query.
>
> http://dev.mysql.com/tech-resources/articles/4.1/subqueries. html
> http://mysqld.active-venture.com/Subqueries.html
>
> You could read about optimizing subqueries for optimum queries.
>
> -----Original Message-----
> From: Kevin Murphy [mailto:php@stubborndonkey.com]
> Sent: Thursday, December 21, 2006 10:49 PM
> To: php-db@lists.php.net
> Subject: [PHP-DB] Order By [blank]
>
> 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
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

------=_Part_59542_8010391.1166723302291--

Re: Order By [blank]

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

Re: Order By [blank]

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

Re: Order By [blank]

------=_Part_59814_14616182.1166724074551
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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
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
>
>

------=_Part_59814_14616182.1166724074551--

Re: Order By [blank]

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
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

Re: Order By [blank]

--Apple-Mail-17--558287788
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

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, 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 > 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
>


--Apple-Mail-17--558287788--