Re: SQL query

On Friday 11 February 2005 13:39, Bruno Wolff III wrote:
> On Fri, Feb 11, 2005 at 11:07:24 +0000,
>
>   David Goodenough <david.goodenough@btconnect.com> wrote:
> > I thought of using an inner select for the join, and using limit 1 to
> > get just the one, and forcing the order by to give me the billing
> > address by preference, but I am then dependant on the sort order
> > of the particular type values I am selecting from.
>
> You can order by boolean expressions such as type = 'billing'.
> You can use that with LIMIT or DISTINCT ON to get just the address you
> want.

Tried this, and got a rather un-intuative answer.  If you have two relevant
entries (one billing, the other default) and you:-

 order by type = 'billing' limit 1

you get the default one, if you:-

 order by type != 'billing' limit 1

you get the billing one.

However:-

  order by type = 'billing' DESC limit 1

does get you the billing one.

It makes sense in that false == 0 and true == 1 in many languages
and 0 sorts before 1, but it still feels wrong.

I had not realised I could use a comparison like this in order by.

Thanks

David