Select most recent row for each order

Question

Currently writing a query which will be used to see where abouts on our production line a certain item is by showing what the last completed 'Resource' was. (resources on our system are just different areas of the assembly line eg, metal fabrication, spray shop, electrical wiring, testing etc.)

I've written a query that gives me ALL completed resources on unfinished jobs, but as each job has several completed resources on it the table that gets output still needs refining.

SELECT 
T0."DocNum", 
T2."U_CustomerRef", 
T1."DocNum",
T6."ItemCode", 
CONCAT(T5."firstName", T5."lastName"), 
T4."U_Time",
T4."U_Date",
T4."U_StopTime",
T4."U_StopDate"
FROM 
ORDR T0  INNER JOIN RDR1 T2 ON T0."DocEntry" = T2."DocEntry" 
LEFT OUTER JOIN "NL_LIVE"."OHEM" "OHEM" ON T0."OwnerCode"="OHEM"."empID" 
LEFT OUTER JOIN OWOR T1 ON T1."OriginAbs" = T0."DocEntry" AND T2."ItemCode" =T1."ItemCode" 
INNER JOIN WOR1 T6 ON T1."DocEntry" = T6."DocEntry"
INNER JOIN OITM T3 ON T2."ItemCode" = T3."ItemCode" 
LEFT OUTER JOIN "NL_LIVE"."@OCHLABBOOK" T4 ON T4."U_ProdOrderNo" = T1."DocNum" AND T4."U_ProdOrderLine"=T6."LineNum"
LEFT OUTER JOIN "NL_LIVE"."OHEM" T5 ON T4."U_StartEmp"=T5."U_UniqueID"
WHERE  
T0."DocStatus"='O'
AND T6."ItemType"=290
AND T4."U_Action"<>'Started' AND T4."U_Action"<>'Stopped' AND T4."U_Action"<>'Paused'

I've tried doing SELECT MAX on the results using the date and time, but that leaves me with only one result in the table as there aren't any groupings for each area it just gives me the latest results for the whole table not each individual job/Document. In this image you can see I've drawn red borders showing that certain parts should be grouped, essentially what i need is a way of selecting the most recent date from each smaller group of data within the Query. Each subset needs to be grouped by Document number (column 3 document number not column 1)

Large shaded red area is just to cover employee names and isn't relevant to the question

Thanks in advance for any help given. If more information is required I can go into more detail of table structures etc.

Answer 1

Since the data model of your SELECT is difficult to understand for me, I prepared following sample

The sample HANA SQL CTE selects the production lines with the products that are manufactured on it. I number each production according to their production date in an descending order on production line basis. For this, I use SQL Row_Number function with Partition By clause

After I filter the CTE data using rn = 1 WHERE clause, I get the most recent production for each production line.

I used LEFT JOIN so the result set displays product line 2 although there is no production on it.

/*
create column table ProductLines (
 pline int,
 productline varchar(100)
);

create column table ProductLineProduction (
 pline int,
 product varchar(10),
 productdatetime datetime,
 quantity int
);
insert into ProductLines values (1,'LineA');
insert into ProductLines values (2,'LineB');
insert into ProductLines values (3,'LineC');

insert into ProductLineProduction values (3,'Hat','201711011010',100);
insert into ProductLineProduction values (1,'Rug','201711031500',5);
insert into ProductLineProduction values (3,'Shirt','201711051200',200);
insert into ProductLineProduction values (1,'Fabric','201711080830',1000);
insert into ProductLineProduction values (3,'Trouser','201711091600',50);
*/

with cte as (
    select *, 
        row_number() over (partition by pl.pline order by p.productdatetime desc) as rn
    from ProductLines pl
    left outer join ProductLineProduction p
        on pl.pline = p.pline
)
select * from cte where rn = 1;

The output for my sample will be as follows