Hi,

Is it possible to join 2 tables and control the sort order of the 2nd table before the join operation?

I have 2 tables as follows:
ProType - ID (Integer), Name (String)
ProVisit - ID (Integer), ProTypeID(Integer), VisitDate(DateTime)

and I want to use a query like this:

SELECT T.ID, V.ProtypeID, V.ID, V.VisitDate from ProType T
join ProVisit V on (V.ProtypeID = T.ID)
GROUP BY T.ID

This produces one row for each unique value of T.ID but that row includes the earliest value of V.VisitDate for that value of T.ID - I want it to
include the latest value of V.VisitDate for that value of T.ID instead.

One way would be to sort the table ProVisit in VisitDate descending order prior to joining. I don't know how to do this and there may be other
options anyway. Any ideas?

"Tony Pomfrett" <tonyp@aline.com.au> wrote in message
news:CD41DA79-4273-4103-96FE-C1D052F5402F@news.elevatesoft.com...
> Hi,
>
> Is it possible to join 2 tables and control the sort order of the 2nd
> table before the join operation?
>
> I have 2 tables as follows:
> ProType - ID (Integer), Name (String)
> ProVisit - ID (Integer), ProTypeID(Integer), VisitDate(DateTime)
>
> and I want to use a query like this:
>
> SELECT T.ID, V.ProtypeID, V.ID, V.VisitDate from ProType T
> join ProVisit V on (V.ProtypeID = T.ID)
> GROUP BY T.ID
>
> This produces one row for each unique value of T.ID but that row includes
> the earliest value of V.VisitDate for that value of T.ID - I want it to
> include the latest value of V.VisitDate for that value of T.ID instead.
>
> One way would be to sort the table ProVisit in VisitDate descending order
> prior to joining. I don't know how to do this and there may be other
> options anyway. Any ideas?
>

There are no guarantees of anything if you sort the input to a query. You
have to use a script, as follows

SELECT MAX(VisitDate) MVisitDate, ProtypeID MProtypeID
into memory\temp
from ProVisit
GROUP BY 2;
CREATE INDEX ByID ON memory\temp(MProtypeID);
SELECT DISTINCT ID, Name, MVisitDate
from ProType
JOIN memory\temp on (MProtypeID = ID);

I use distinct because I assume if you have multiple visits on the same
date, you only want one.

Robert

Thanks Robert. I'll give it a try.