Run maxrow queries

gistfile1.txt

set long_query_time=0;

# Query with loose index scan optimization
SELECT max(pk), j FROM tq GROUP BY j;

# Uncorrelated subquery
SELECT t1.pk, t1.j, t1.k
FROM tq t1, (SELECT max(pk) as maxpk, j FROM tq GROUP BY j) t2
WHERE t2.maxpk = t1.pk;

# Correlated subquery
SELECT pk, j, k FROM  tq t1
WHERE  pk=(SELECT MAX(t2.pk) FROM tq t2 WHERE t1.j = t2.j);

# Left join
SELECT t1.pk, t1.j, t1.k FROM tq t1
LEFT JOIN tq t2 ON t1.j = t2.j AND t1.pk < t2.pk
WHERE t2.j IS NULL;

# rank() window function with DESC
WITH t1 AS (SELECT pk, j, k,
    RANK() OVER (PARTITION by j ORDER BY pk DESC) AS myrank FROM tq)
SELECT pk, j, k from t1 WHERE myrank=1;

# rank() window function with DESC and index hint

WITH t1 AS (SELECT /*+ INDEX(tq x) */ pk, j, k,
    RANK() OVER (PARTITION by j ORDER BY pk DESC) AS myrank FROM tq)
SELECT /*+ INDEX(tq x) */ pk, j, k from t1 WHERE myrank=1;