SQL: "ORDER BY clause conflicts with DISTINCT" please help

[Arrowx7]

Hello,
I've been at this for many hours, if you can give me help I would greatly appreciate it. This is the query:

SELECT DISTINCT tblPersonnel.PID, (select distinct top 1 tblAppointments.rank from tblAppointments where tblAppointments.PID=tblPersonnel.PID order by tblAppointments.start) AS Expr1
FROM tblPersonnel;

Error: ORDER BY clause conflicts with DISTINCT


The problem is in the subquery, I wanted to return the oldest record in the table, but I only want it to return "rank" field.

Here is what I tried:

If I remove Distinct from the subquery i get "only one record can be returned from the subquery" even though it says "top 1"

If I change tblAppointments.rank to tblAppointments.start it works perfectly, but it outputs the start date as opposed to the rank field.

If I put both rank and start in, it complains saying subquery can only return one field

I dont' know what else to try, please help!

 

[dhookom]

Do you get any records returned from:

SELECT PID, Start
FROM tblAppointments
GROUP BY PID, Start
HAVING COUNT(Rank) > 1

If so, you need to break the tie by include the Rank in the ORDER BY of the subquery and removing the DISTINCT.


Duane
Hook'D on Access
MS Access MVP