0

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

2
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.