Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Run maxrow queries
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.