MariaDB order by 在 Group by 之前

我環顧了其他解決方案，但似乎沒有一個工作得很好。

我要做的是從選擇中獲取最新結果（按日期排序），按樣本分組。

這是我的部分 ERD（請原諒 ascii）：

                 +--------+
                 | sample |
                 +--------+        +--------+
+--------+      ->| ID     |        | batch  |
| result |     /  | text   |        +--------+
+--------+    /   | batch  |------->| ID     |
| ID     |   /    +--------+        | text   |
| text   |  /                       +--------+
| sample |--      +--------+
| test   |---     | test   |
| date   |   \    +--------+
+--------+    --->| ID     |
                 | text   |
                 +--------+

和 DDL：

create table result (resultid int(11), resulttext varchar(20), resultsample int(11), resulttest int(11), resultdate datetime);
create table sample (sampleid int(11), sampletext varchar(20), samplebatch int(11));
create table batch (batchid int(11), batchtext varchar(20));
create table test (testid int(11), testtext varchar(20));

insert into batch(batchid, batchtext) values(12759, 'batch 12759');
insert into sample(sampleid, sampletext, samplebatch) values(99787, 'sample 99787', 12759);
insert into sample(sampleid, sampletext, samplebatch) values(99786, 'sample 99786', 12759);
insert into test(testid, testtext) values(2144, 'test 2144');
insert into result(resultid, resulttext, resultsample, resulttest, resultdate) values(1, "Pass", 99787, 2144, "2018-02-01 11:02:18");
insert into result(resultid, resulttext, resultsample, resulttest, resultdate) values(2, "Fail", 99787, 2144, "2018-02-01 09:01:34");
insert into result(resultid, resulttext, resultsample, resulttest, resultdate) values(3, "Pass", 99786, 2144, "2018-02-01 08:06:12");

這是一個 SQL 查詢（只是為了看看那裡有什麼）：

select
   resulttext,
   resultdate,
   sampleid
from
   batch Right Join(
       sample Right Join(
           test Right Join
           result On testid = resulttest
       ) On sampleid = resultsample
   ) On batchid = samplebatch
where
   batchid = 12759 and
   testid  = 2144
order by
   resultdate desc

這是我的結果集的範例（來自上述查詢）：

resulttext resultdate          sampleid
---------- ----------          ---------
Pass       2018-02-01 11:02:18 99787
Fail       2018-02-01 09:01:34 99787
Pass       2018-02-01 08:06:12 99786

所以我希望在按sampleid分組之前對resultdate進行排序（獲取最新的）。我的查詢目前是：

select
   *
from (
   select
       resulttext,
       resultdate,
       sampleid,
       batchid,
       testid
   from
       batch Right Join(
           sample Right Join(
               test Right Join
               result On testid = resulttest
           ) On sampleid = resultsample
       ) On batchid = samplebatch
   order by
       resultdate desc
) x
where
   batchid = 12759 and
   testid  = 2144
group by
   sampleid

但我得到的結果集不正確：

resulttext resultdate          sampleid
---------- ----------          ---------
Pass       2018-02-01 08:06:12 99786
Fail       2018-02-01 09:01:34 99787

它應該得到 11:02 的結果（其中 sampleid=99787），但它得到了錯誤的結果。有人可以幫我查詢嗎？

我正在使用 MariaDB 15.1

使用類似的東西

SELECT result.text, result.date, sample.ID
FROM       ( SELECT sample, MAX(date) date 
            FROM result 
            GROUP BY sample) maxtime
INNER JOIN result  ON result.sample=maxtime.sample
                 AND result.date=maxtime.date
LEFT JOIN  test ON test.ID = result.test
LEFT JOIN  sample ON sample.ID = result.sample
LEFT JOIN  batch ON batch.ID = sample.batch
WHERE batch.ID = 12759 
 AND test.ID  = 2144

引用自：https://dba.stackexchange.com/questions/215051