SQL and JPQL query - searching all records grouping by parameter with the given date

Question

I have table which looks similar to this:

I want to build query for searching all records from this table with the given date (let's say 5.12.2019) and with earlier dates but group by materialID.

Example: select all materials with date 6.12.2019 should show all materials with this date (or materials with earlier dates) group by material id with the biggest date Result should look like this:

Problem: I want to group my results by MaterialID with the biggest date. So in this example I don't want to show materials with the same id with earlier dates.

For the same example:

Question: How to build query like this using SQL and also JPQL? Because i would like to use this query in Hibernate so i need also JPQL query.

Thanks for your help.

Answer 1

This is a special case of a "top N per category" query. You want to show the maximum date per material id. In SQL (would also work in JPQL):

SELECT SUM(Amount), SUM(Price), MaterialId, MAX(Date)
FROM t
GROUP BY MaterialId

Note that with this technique, you cannot also display the ID, or MAX(ID), as the IDs and dates are not necessarily both monotonously increasing. If you still want the ID displayed as in your example, then write this SQL query (I don't think this can be done in JPQL):

SELECT MAX(ID), SUM(Amount), SUM(Price), MaterialId, MAX(Date)
FROM (
  SELECT last_value(ID) OVER (
    PARTITION BY MaterialId 
    ORDER BY Date, ID 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS ID,
  Amount,
  Price,
  MaterialId,
  SELECT last_value(Date) OVER (
    PARTITION BY MaterialId 
    ORDER BY Date, ID 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS Date
  FROM t
) t
GROUP BY MaterialId