How to write subquery in Criteria

Question

I have a SQL like this:

Select tbl.id, tbl.name 
From 
 (select table1.id, table1.name
 from table1
 inner join table2 on table1.id = table2.id
 order by table2.priority
 ) tbl 
group by table1.id
order by table1.name

What I'm trying to achieve is to first sort (order by table2.priority), and then get the record with table1.id, name with highest priority. Note, MAX(table2.priority) doesn't work here, because table1 to table2 is one to many, and for one table1 record, table2 can have N records with the highest priority = 1, where another table1 record with highest priority = 3.

Answer 1

If you only need one record from the result, and they are in order such that the record you need is at the end (or beginning) of the sort, simply limit the results to one. i.e:

SELECT tbl.id, tbl.name
FROM (
    SELECT table1.id, table1.name
    FROM table1
    INNER JOIN table2 ON table1.id = table2.id
    ORDER BY table2.priority
) tbl
GROUP BY table1.id
ORDER BY table1.name
LIMIT 1;

Note that depending on the order, you could specify ASC or DESC to ensure the correct record is the one you retrieve.