I have one table (table1) with columns:

and another table (table2) with columns:

I am trying to write a query that will return distinct rows from table1 where status from table2 is ACTIVE and results should be ordered by min DATE from table2. Desired result:

I tried with the following:

select t1, min(t2.date) 
from table1 t1 join t1.t2List t2  -- table1 Entity has OneToMany t2List defined
where t2.status = 'ACTIVE'
group by t1.id
order by t2.date desc;

Problem here is that I can't use my Entity class table1 and I would like to avoid creating a new class that will hold this additional aggregated result (min date).

Also tried using HAVING clause but could not get it working.

select t1 from table1 t1
where t1.id in (
select table1.id from table2 t2 where t2.status = 'ACTIVE'
group by t1.id, t2.date
having t2.date = min(t2.date));

Appriciete any help here!

CodePudding user response：

Join table1 to a query that aggregates in table2 and returns all table1_ids with an ACTIVE row:

SELECT t1.*
FROM table1 t1
INNER JOIN (
  SELECT table1_id, MIN(date) date
  FROM table2
  WHERE status = 'ACTIVE'
  GROUP BY table1_id
) t2 ON t2.table1_id = t1.id
ORDER BY t2.date;

See the demo.

CodePudding user response：

There are multiple ways of solving this problem, as @forpas has already answered using a subquery in the JOIN clause.

The same results can be achieved using this query

SELECT table1.ID , table1.name
FROM table1  
INNER JOIN table2 ON table2.table1_ID = table1.ID
WHERE status = 'ACTIVE'
GROUP BY table1.ID , table1.name
ORDER BY MIN(table2.statusDate)