恼人的非单组分组函数的SQL语句
select MAX(t3.ORDER_NO),t1.BED_NO,t2.NAME,
t3.START_DATE_TIME,
t3.ORDER_TEXT,
from PATS_IN_HOSPITAL t1,PAT_MASTER_INDEX t2,ORDERS t3 where
t1.PATIENT_ID
=t2.PATIENT_ID and t1.PATIENT_ID=t3.PATIENT_ID "
就是上面这个SQL语句，我写好了，出现错误提示为：“非单组分组函数”，我也知道错误出在MAX这个地方，但是，我本意就是要找出t3表中的最大的ORDER_NO的记录然后与别的两个表t1和t2由三个表都相同的PATIENT_ID对应起来。
总之问题就是出在MAX这个地方，请问我应该怎么改呢？谢谢高手
[解决办法]
try
select t3.ORDER_NO,t1.BED_NO,t2.NAME,
t3.START_DATE_TIME,
t3.ORDER_TEXT
from PATS_IN_HOSPITAL t1,PAT_MASTER_INDEX t2,
ORDERS t3,
(Select MAX(ORDER_NO) From t3) t4
where
t1.PATIENT_ID
=t2.PATIENT_ID and t1.PATIENT_ID=t3.PATIENT_ID and t3.ORDER_NO = t4.ORDER_NO
[解决办法]
select t3.ORDER_NO,t1.BED_NO,t2.NAME,
t3.START_DATE_TIME,
t3.ORDER_TEXT
from (select PATIENT_ID,ORDER_NO from ORDERS where ORDER_NO=(select max(ORDER_NO)from ORDERS))t3
left join
PAT_MASTER_INDEX t2
on t2.PATIENT_ID=t3.PATIENT_ID
left join
PATS_IN_HOSPITAL t1
on t1.PATIENT_ID=t2.PATIENT_ID
