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