How to get the data from max of two fields

Question

The question here is similar:

SQL Selecting multiple columns based on max value in one column

but in that question, it suggests sql. I want to do in java side.

In java spring boot, i wrote this:

 Qmk104 findFirstBypakAndpolOrderBytecDesczeyDesc(String pak, int pol)

so with that, i can return the row with max tec which has max zey also.

After that, i will get tec and zey:

int maxtec;
int maxzey;

and with those, i will make a query again to get the data with max tec which has max zey.

tec zey  pak 
0   1     24
1   2    24
2   0    25
2   1    25
4   5    26
5   0    27
5   1    28
5   2     2(this is max tec with zey. max tec= 5. tec 5 has max 1 zey)
5   2     4(but i need also this because max tec with zey so i will make another query to get  both)

in sql, i can get easy

select * from qmk104 f where POL=390097 and PAK='K'and
        (TEC) in
        (select max(t.TEC) from qmk104 t
         where t.POL=f.POL and t.PAK=f.PAK)
                                and ZEY in (
        select max(t.ZEY) from qmk104 t
        where t.POL=f.POL and t.PAK=f.PAK and t.TEC = f.TEC
    );

how can get it in spring data? With criteria?

Answer 1

My knowledge of the Criteria API is basically non-existent, but I can offer the following query which uses exists logic rather than ORDER BY:

SELECT *
FROM qmk104 t1
WHERE NOT EXISTS (SELECT 1 FROM qmk104 t2
                  WHERE t2.tec > t1.tec OR
                        t2.tec = t1.tec AND t2.zey > t1.zey);

Demo

You might need a native query to make the above work, as JPQL does not seem to directly supports EXISTS. While we're discussing native queries, another good way to approach this would be to use RANK:

SELECT *
FROM
(
    SELECT *, RANK() OVER (ORDER BY tec DESC, zey DESC) rnk
    FROM qmk104
) t
WHERE rnk = 1;

But again, you would need a native query here, or you could place the above code into a stored procedure and then call it from Java.