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(dot)goodenough(at)btconnect(dot)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