thanks Svein,

Interesting. I had already tried the 'order by p.name' but got a complaint
because i had wrongly assumed that i used the alias in the order by clause.

Kind regards,
Pete

> -----Original Message-----
> From: Svein Erling Tysvar
> [mailto:svein.erling.tysvaer@...]
> Sent: 03 July 2001 11:27
> To: ib-support@yahoogroups.com
> Subject: Re: [ib-support] join-sort question
>
>
> >select p.*, s.* from patients p left outer join patientstatus s
> >on ((p.id=s.id) and (p.name=s.name)) order by name
>
> >trouble is this returns the rows that have matching records in
> both tables
> >in order and then the records that only have only data in the
> primary table
> >in no particular order.
>
> Simple one. You order by name, but don't specify which name (patients or
> patientstatus). IB selects the wrong name, and since some of the records
> doesn't have anything in this field, they're ordered randomly. Just change
> your query to
>
> select p.*, s.* from patients p left outer join patientstatus s
> on ((p.id=s.id) and (p.name=s.name)) order by patients.name
>
> and you should be fine.
>
> Set
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>