order by with variable

hello,

I have listings with coordinates, i would like to do ORDER BY and
display listings with coords first. If they don't have coords the value
is blank and the coords go both positive and negative values. So i
tried an "ORDER by l_xcoord HAVING l_xcoord<>0" but this is acting like
a WHERE statement removing all results with no coords. I tried GROUP BY
as well but same results...

So the question is how can i display all results with the ones that
have coords first? It seems like it should be simple but I'm just not
seeing it.

Thanks,

Chad

Re: order by with variable

>I have listings with coordinates, i would like to do ORDER BY and
>display listings with coords first.
>If they don't have coords the value
>is blank and the coords go both positive and negative values.

If they don't have coords the values that the coords don't have
can be positive or negative? Huh? Also, there's no such value
as "blank" for a numeric field. Null, yes, blank, no.

>So i
>tried an "ORDER by l_xcoord HAVING l_xcoord<>0" but this is acting like
>a WHERE statement removing all results with no coords. I tried GROUP BY
>as well but same results...

ORDER BY if(l_xcoord<>0, 0, 1), l_xcoord

should do what I think you want.

>
>So the question is how can i display all results with the ones that
>have coords first? It seems like it should be simple but I'm just not
>seeing it.

The trick here is to come up with an expression you can put earlier
in the sort order which distinguishes the cases you want.
if(l_xcoord<>0, 0, 1) has the value 0 if there are coordinates, 1
if there are not.

Gordon L. Burditt

Re: order by with variable

Hey Gordon,

I didn't know you could put if() ststements into a SELECT. I will give
that a try. yes NULL not blank thanks for that, and the coords in
general are both positive and negative values, and if it is blank or
NULL obviously it wouldn't be + or - .

how does the if work exactly in a SELECT? you wrote "ORDER BY
if(l_xcoord<>0, 0, 1), l_xcoord" So we have if(item<>value, 0, 1)item
does this mean 0 is like true and 1 is false or do the numbers mean
something else like number value lengths??? And then i just put the
order by l_xcoord after it? Can you explain how it works?

I use if statements in php but don't recognize the 0, 1 in it.

Thanks,

Chad

Gordon Burditt wrote:
> >I have listings with coordinates, i would like to do ORDER BY and
> >display listings with coords first.
> >If they don't have coords the value
> >is blank and the coords go both positive and negative values.
>
> If they don't have coords the values that the coords don't have
> can be positive or negative? Huh? Also, there's no such value
> as "blank" for a numeric field. Null, yes, blank, no.
>
> >So i
> >tried an "ORDER by l_xcoord HAVING l_xcoord<>0" but this is acting like
> >a WHERE statement removing all results with no coords. I tried GROUP BY
> >as well but same results...
>
> ORDER BY if(l_xcoord<>0, 0, 1), l_xcoord
>
> should do what I think you want.
>
> >
> >So the question is how can i display all results with the ones that
> >have coords first? It seems like it should be simple but I'm just not
> >seeing it.
>
> The trick here is to come up with an expression you can put earlier
> in the sort order which distinguishes the cases you want.
> if(l_xcoord<>0, 0, 1) has the value 0 if there are coordinates, 1
> if there are not.
>
> Gordon L. Burditt

Re: order by with variable

i just tried it and your example works great. I would still like to
understand it more so i can use it further.

what if you wanted to do the same thing but with a work.

like you searched the listings for a keyword, and wanted to return a
weighted result.

If the table has a title, and a description which we will call l_title
and l_desc and you search looking for a keyword so, where l_title or
l_desc = 'keyword' and then want to order by if the keyword matches the
title first and then the desc. Wouldn;t this give "weight to the title"
I just dont understand the 0, 1 at the end of the if yet to modify it.

Could something like this work?

Thanks for your help.

Chad

chadsmith76@gmail.com wrote:
> Hey Gordon,
>
> I didn't know you could put if() ststements into a SELECT. I will give
> that a try. yes NULL not blank thanks for that, and the coords in
> general are both positive and negative values, and if it is blank or
> NULL obviously it wouldn't be + or - .
>
> how does the if work exactly in a SELECT? you wrote "ORDER BY
> if(l_xcoord<>0, 0, 1), l_xcoord" So we have if(item<>value, 0, 1)item
> does this mean 0 is like true and 1 is false or do the numbers mean
> something else like number value lengths??? And then i just put the
> order by l_xcoord after it? Can you explain how it works?
>
> I use if statements in php but don't recognize the 0, 1 in it.
>
> Thanks,
>
> Chad
>
> Gordon Burditt wrote:
> > >I have listings with coordinates, i would like to do ORDER BY and
> > >display listings with coords first.
> > >If they don't have coords the value
> > >is blank and the coords go both positive and negative values.
> >
> > If they don't have coords the values that the coords don't have
> > can be positive or negative? Huh? Also, there's no such value
> > as "blank" for a numeric field. Null, yes, blank, no.
> >
> > >So i
> > >tried an "ORDER by l_xcoord HAVING l_xcoord<>0" but this is acting like
> > >a WHERE statement removing all results with no coords. I tried GROUP BY
> > >as well but same results...
> >
> > ORDER BY if(l_xcoord<>0, 0, 1), l_xcoord
> >
> > should do what I think you want.
> >
> > >
> > >So the question is how can i display all results with the ones that
> > >have coords first? It seems like it should be simple but I'm just not
> > >seeing it.
> >
> > The trick here is to come up with an expression you can put earlier
> > in the sort order which distinguishes the cases you want.
> > if(l_xcoord<>0, 0, 1) has the value 0 if there are coordinates, 1
> > if there are not.
> >
> > Gordon L. Burditt

Re: order by with variable

>i just tried it and your example works great. I would still like to
>understand it more so i can use it further.

if(expression, truevalue, falsevalue)
is an expression. (forget about an if *statement* here or the
syntax of one). It has the value truevalue if expression is not
equal to 0 and not null and the value falsevalue of expression is
0 or null. It can be used where an expression can be used.

Note that
if(x = 2, 1, 0)
and
if(x != 2, 0, 1)
and
x = 2
are different if x is null (and otherwise they are equal).

Another one you might want to use is
ifnull(expression1, expression2)
which returns expression1 if expression1 is not null, otherwise
it returns expression2 (so expression2 is the "default value" that
replaces a null value in expression1).


ORDER BY lets you sort the result based on an expression. For example,

ORDER BY (xcoord - 5.0)*(xcoord - 5.0) + (ycoord - 2.0)*(ycoord - 2.0)

orders by the distance (well, actually here, it's the SQUARE of the
distance, since square roots are expensive and using one won't
change the order) from (5.0, 2.0).


>what if you wanted to do the same thing but with a work.

Huh? You mean homework?

>like you searched the listings for a keyword, and wanted to return a
>weighted result.

You can do things like:

order by case b.politics
when 'republicrat' then 1
when 'demopublican' then 2
when 'libertarian' then 3
when 'communist' then 4
when 'fascist' then 5
when 'asshole' then 6
when 'jerk' then 7
else 0
end

where all that stuff from case ... end is just another expression. It
would sort things in the order:

somethingelse
republicrat
demopublican
libertarian
communist
fascist
asshole
jerk



>If the table has a title, and a description which we will call l_title
>and l_desc and you search looking for a keyword so, where l_title or
>l_desc = 'keyword' and then want to order by if the keyword matches the
>title first and then the desc. Wouldn;t this give "weight to the title"
>I just dont understand the 0, 1 at the end of the if yet to modify it.
>
>Could something like this work?

I'm not sure I understand what you are asking for, but something
like:

order by 2*(l_title = 'keyword') + 1*(l_desc = 'keyword') desc

would put the records in order:
both title and description match
title only match
description only match
neither match
.. Assuming that l_title and l_desc are NOT NULL fields.
if() is useful for defaulting the NULL case one way or the other.

Another possiblity is:
order by if(l_title='keyword', 0, if(l_desc = 'keyword', 1, 2))
which puts things in the order:
title match (0)
description match but no title match (1)
neither match or null (2)

Gordon L. Burditt

Re: order by with variable

Thanks Gordon, you have just answered huge questions for me, and opened
new doors in my limited skills. I really appreciate it.

Chad


Gordon Burditt wrote:
> >i just tried it and your example works great. I would still like to
> >understand it more so i can use it further.
>
> if(expression, truevalue, falsevalue)
> is an expression. (forget about an if *statement* here or the
> syntax of one). It has the value truevalue if expression is not
> equal to 0 and not null and the value falsevalue of expression is
> 0 or null. It can be used where an expression can be used.
>
> Note that
> if(x = 2, 1, 0)
> and
> if(x != 2, 0, 1)
> and
> x = 2
> are different if x is null (and otherwise they are equal).
>
> Another one you might want to use is
> ifnull(expression1, expression2)
> which returns expression1 if expression1 is not null, otherwise
> it returns expression2 (so expression2 is the "default value" that
> replaces a null value in expression1).
>
>
> ORDER BY lets you sort the result based on an expression. For example,
>
> ORDER BY (xcoord - 5.0)*(xcoord - 5.0) + (ycoord - 2.0)*(ycoord - 2.0)
>
> orders by the distance (well, actually here, it's the SQUARE of the
> distance, since square roots are expensive and using one won't
> change the order) from (5.0, 2.0).
>
>
> >what if you wanted to do the same thing but with a work.
>
> Huh? You mean homework?
>
> >like you searched the listings for a keyword, and wanted to return a
> >weighted result.
>
> You can do things like:
>
> order by case b.politics
> when 'republicrat' then 1
> when 'demopublican' then 2
> when 'libertarian' then 3
> when 'communist' then 4
> when 'fascist' then 5
> when 'asshole' then 6
> when 'jerk' then 7
> else 0
> end
>
> where all that stuff from case ... end is just another expression. It
> would sort things in the order:
>
> somethingelse
> republicrat
> demopublican
> libertarian
> communist
> fascist
> asshole
> jerk
>
>
>
> >If the table has a title, and a description which we will call l_title
> >and l_desc and you search looking for a keyword so, where l_title or
> >l_desc = 'keyword' and then want to order by if the keyword matches the
> >title first and then the desc. Wouldn;t this give "weight to the title"
> >I just dont understand the 0, 1 at the end of the if yet to modify it.
> >
> >Could something like this work?
>
> I'm not sure I understand what you are asking for, but something
> like:
>
> order by 2*(l_title = 'keyword') + 1*(l_desc = 'keyword') desc
>
> would put the records in order:
> both title and description match
> title only match
> description only match
> neither match
> . Assuming that l_title and l_desc are NOT NULL fields.
> if() is useful for defaulting the NULL case one way or the other.
>
> Another possiblity is:
> order by if(l_title='keyword', 0, if(l_desc = 'keyword', 1, 2))
> which puts things in the order:
> title match (0)
> description match but no title match (1)
> neither match or null (2)
>
> Gordon L. Burditt