How to write subquery in Criteria

Question

Hi there,

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 tring to archieve 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. Thanks in advance!

David

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.