show first record per each id (1 Viewer)

Peter Reid said:

Unless I've misinterpreted your problem, I don't see why you have used a UNION SELECT in your SQL

I would use subqueries. First we need to know the earliest due date per machine, I've used Min instead of First.

As it may be possible to have more than one task per due date (with the info you've given), you'd also need to know the earliest ID per the earliest due date

Therefore the SQL would look something like

Code:Copy to clipboard

SELECT td.* 
FROM (SELECT td2.machinenum, td2.MinOfduedate_task, Min(td3.ID_task) AS MinOfID_task
      FROM (SELECT td1.machinenum, Min(td1.duedate_task) AS MinOfduedate_task
            FROM tasks_diary AS td1
            GROUP BY td1.machinenum) AS td2 
        INNER JOIN tasks_diary AS td3 ON (td2.MinOfduedate_task = td3.duedate_task) AND (td2.machinenum = td3.machinenum)
      GROUP BY td2.machinenum, td2.MinOfduedate_task) as td;

(add related task table fields as desired)

Click to expand...

SELECT td2.* , tasks.name_task
 FROM (SELECT td1.machinenum, td1.ID_task, Min(td1.duedate_task) AS MinOfduedate_task
            FROM tasks_diary AS td1
WHERE (((td1.dodate_task) Is Null) AND ((td1.duedate_task)<=DateAdd("h",1,Now())) AND ((td1.lognum) Like "*" & [Tempvars]![uniqcode] & "*" Or (td1.lognum) In (SELECT  ID_worker  FROM workers_list WHERE Level=1) Or (td1.lognum)=999999) AND ((td1.statustask)=0 Or (td1.statustask)=4)) 
            GROUP BY td1.machinenum, td1.ID_task) AS td2 LEFT JOIN tasks ON td2.ID_task=tasks.ID
UNION SELECT tasks_diary.ID_task, tasks_diary.duedate_task, tasks.name_task, tasks_diary.machinenum
FROM tasks INNER JOIN tasks_diary ON tasks.ID = tasks_diary.ID_task
WHERE (((tasks_diary.duedate_task)<=Now()) AND (tasks.typef=9) AND (tasks_diary.dodate_task>=Now()));

arnelgp said:

you should hsve made your answer complete. there are conditions there. the op will be confused with those alias if he tried to insert his own.

Click to expand...

arnelgp said:

not tested:


SELECT tasks_diary.ID_task,
(SELECT TOP 1 T1.duedate_task FROM tasks_diary AS T1 WHERE T1.ID_task=tasks_diary.ID_task AND T1.machinenum=tasks_diary.machinenum ORDER BY T1.duedate_task ASC) As duedate,
tasks.name_task,
tasks_diary.machinenum
FROM tasks_diary INNER JOIN tasks ON tasks_diary.ID = tasks.ID_task
WHERE (((tasks_diary.dodate_task) Is Null) AND ((tasks_diary.duedate_task)<=DateAdd("h",1,Now())) AND ((tasks_diary.lognum) Like "*" & [Tempvars]![uniqcode] & "*" Or (tasks_diary.lognum) In (SELECT ID_worker FROM workers_list WHERE Level=1) Or (tasks_diary.lognum)=999999) AND ((tasks_diary.statustask)=0 Or (tasks_diary.statustask)=4))
group by tasks_diary.ID_task, tasks.name_task, tasks_diary.machinenum
UNION ALL
SELECT tasks_diary.ID_task, tasks_diary.duedate_task, tasks.name_task, tasks_diary.machinenum
FROM tasks INNER JOIN tasks_diary ON tasks.ID = tasks_diary.ID_task
WHERE (((tasks_diary.duedate_task)<=Now()) AND (tasks.typef=9) AND (tasks_diary.dodate_task>=Now()));

Click to expand...