Select most recent row for each order

greatest-n-per-groupsap-hana

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.

Best Answer

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

Simple solution

With DISTINCT ON in Postgres:

SELECT DISTINCT ON (i.good, i.the_date)
       i.the_date, p.the_date AS pricing_date, i.good, p.price
FROM   inventory  i
LEFT   JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER  BY i.good, i.the_date, p.the_date DESC;

Returned rows are ordered. See:

Select first row in each GROUP BY group?

Or with NOT EXISTS in standard SQL (works with every RDBMS I know):

SELECT i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price
FROM   inventory  i
LEFT   JOIN price p ON p.good = i.good AND p.the_date <= i.the_date
WHERE  NOT EXISTS (
   SELECT FROM price p1
   WHERE  p1.good = p.good
   AND    p1.the_date <= i.the_date
   AND    p1.the_date >  p.the_date
   );

Same result, but with arbitrary sort order - unless you add ORDER BY.
Depending on data distribution, exact requirements and indices, either one of these may be faster. See:

How do I (or can I) SELECT DISTINCT on multiple columns?

With only few rows per good, DISTINCT ON is typically faster and you get a sorted result on top of it. But for certain cases other query techniques are (much) faster, yet. See below.

Solutions with subqueries to compute max / min values are typically slower. Variants with CTEs are generally slower, yet. (CTEs improved with Postgres 12.)

Plain views (like proposed by another answer) do not help performance at all in Postgres.

db<>fiddle here
_{Old sqlfiddle}

Proper solution

Strings and collation

First of all, your table layout is a sub-optimal. It may seem trivial, but normalizing your schema can go a long way.

Sorting by character types (text, varchar, ...) is done according to current COLLATION. Typically, your DB would use some local set of rules, like in my case: de_AT.UTF-8. Find out with:

SHOW lc_collate;

This makes sorting and index look-ups slower. The longer your strings (names of goods) the worse. If you do not actually care for collation rules in your output (or the sort order), this can be faster with COLLATE "C":

SELECT DISTINCT ON (i.good COLLATE "C", i.the_date)
       i.the_date, p.the_date AS pricing_date, i.good, p.price
FROM   inventory  i
LEFT   JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER  BY i.good COLLATE "C", i.the_date, p.the_date DESC;

Note the added collation in two places.
Twice as fast in my test with 20k rows each and very basic names ('good123').

Index

If your query is supposed to use an index, columns with character data have to use a matching collation (good in the example):

CREATE INDEX inventory_good_date_desc_collate_c_idx
ON price(good COLLATE "C", the_date DESC);

Read the last two chapters of the related answer I linked above.

You can even have multiple indexes with different collations on the same columns - if you also need goods sorted according to another (or the default) collation in other queries.

Normalize

Redundant strings (name of good) bloat tables and indexes, which makes everything slower. A proper table layout can avoid most of the problem. Could look like this:

CREATE TABLE good (
  good_id serial PRIMARY KEY
, good    text   NOT NULL
);

CREATE TABLE inventory (
  good_id  int  REFERENCES good (good_id)
, the_date date NOT NULL
, quantity int  NOT NULL
, PRIMARY KEY(good_id, the_date)
);

CREATE TABLE price (
  good_id  int     REFERENCES good (good_id)
, the_date date    NOT NULL
, price    numeric NOT NULL
, PRIMARY KEY(good_id, the_date));

The primary keys automatically provide (almost) all indices we need.
Depending on missing details, a multicolumn index on price with descending order on the second column may improve performance:

CREATE INDEX price_good_date_desc_idx ON price(good, the_date DESC);

Again, the collation must match your query (see above).

Since Postgres 9.2 "covering indices" for index-only scans can help some more - especially if tables hold additional columns, making the table substantially bigger than the index.

These resulting queries are much faster:

DISTINCT ON

SELECT DISTINCT ON (i.the_date)
       i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price
FROM   inventory  i
JOIN   good       g USING (good_id)
LEFT   JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date
ORDER  BY i.the_date, p.the_date DESC;

NOT EXISTS

SELECT i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price
FROM   inventory  i
JOIN   good       g USING (good_id)
LEFT   JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date
AND    NOT EXISTS (
   SELECT 1 FROM price p1
   WHERE  p1.good_id = p.good_id
   AND    p1.the_date <= i.the_date
   AND    p1.the_date >  p.the_date
   );

db<>fiddle here
_{OLD sqliddle}

Faster solutions

If that still is not fast enough, there may be faster solutions.

Recursive CTE / `JOIN LATERAL` / correlated subquery

Especially for data distributions with many prices per good:

Optimize GROUP BY query to retrieve latest record per user

Materialized view

If you need to run this often and fast, I suggest you create a materialized view. I think it is safe to assume, that prices and inventories for past dates rarely change. Compute the result once and store a snapshot as materialized view.

Postgres 9.3+ has automated support for materialized views. You can easily implement a basic version in older versions.

SQL Server – How to Select All Most Recent Records

Your query includes a lot of tables and columns that aren't listed in your sample data. From this query you can join and pull in other things:

;WITH lastMessage AS
(
    SELECT Id, IdMain, IdReply, DatePosted, rn = ROW_NUMBER() OVER 
      (PARTITION BY COALESCE(IdMain, Id) ORDER BY DatePosted DESC, Id)
    FROM dbo.Message
)
SELECT t.Id, t.IdMessage, m.IdMain, m.IdReply, m.DatePosted
  FROM lastMessage AS m
  INNER JOIN dbo.Target AS t
  ON m.Id = t.IdMessage
  WHERE m.rn = 1;

SQLfiddle demo