MariaDB – Order By Before Group By Explained

greatest-n-per-groupmariadbMySQL

I have looked around at other solutions, but none seems to work quite right.
What I am trying to do is to get the latest result (sorted by date) from a selection, grouped together by sample.

Here is my partial ERD (excuse the ascii):

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

And the 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");

Here is an SQL query (just to see whats there):

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

Here is an example of my result set (from the above query):

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

So I wish to sort on resultdate (get the latest one) before grouping by sampleid. My query is currently:

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

But the result set I get is incorrect:

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

It should have got the 11:02 result (where sampleid=99787), but it gets the wrong one. Can someone help with my query?

I am using MariaDB 15.1

Best Answer

Use something like

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
Related Question