output for queries

gistfile1.txt

--------------
set long_query_time=0
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
SELECT max(pk), j FROM tq GROUP BY j
--------------

+---------+----+
| max(pk) | j  |
+---------+----+
|    8192 |  1 |
|   24575 |  2 |
|   40958 |  3 |
|   57341 |  4 |
|   73724 |  5 |
|   90107 |  6 |
|  106490 |  7 |
|  122873 |  8 |
|  139256 |  9 |
|  155639 | 10 |
+---------+----+
10 rows in set (0.00 sec)

--------------
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
--------------

+--------+----+----+
| pk     | j  | k  |
+--------+----+----+
|   8192 |  1 | 92 |
|  24575 |  2 | 75 |
|  40958 |  3 | 58 |
|  57341 |  4 | 41 |
|  73724 |  5 | 24 |
|  90107 |  6 |  7 |
| 106490 |  7 | 90 |
| 122873 |  8 | 73 |
| 139256 |  9 | 56 |
| 155639 | 10 | 39 |
+--------+----+----+
10 rows in set (0.00 sec)

--------------
SELECT pk, j, k FROM  tq t1
WHERE  pk=(SELECT MAX(t2.pk) FROM tq t2 WHERE t1.j = t2.j)
--------------

+--------+----+----+
| pk     | j  | k  |
+--------+----+----+
|   8192 |  1 | 92 |
|  24575 |  2 | 75 |
|  40958 |  3 | 58 |
|  57341 |  4 | 41 |
|  73724 |  5 | 24 |
|  90107 |  6 |  7 |
| 106490 |  7 | 90 |
| 122873 |  8 | 73 |
| 139256 |  9 | 56 |
| 155639 | 10 | 39 |
+--------+----+----+
10 rows in set (2 min 14.11 sec)

--------------
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
--------------

+--------+----+----+
| pk     | j  | k  |
+--------+----+----+
|   8192 |  1 | 92 |
|  24575 |  2 | 75 |
|  40958 |  3 | 58 |
|  57341 |  4 | 41 |
|  73724 |  5 | 24 |
|  90107 |  6 |  7 |
| 106490 |  7 | 90 |
| 122873 |  8 | 73 |
| 139256 |  9 | 56 |
| 155639 | 10 | 39 |
+--------+----+----+
10 rows in set (1.07 sec)

--------------
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
--------------

+--------+----+----+
| pk     | j  | k  |
+--------+----+----+
|   8192 |  1 | 92 |
|  24575 |  2 | 75 |
|  40958 |  3 | 58 |
|  57341 |  4 | 41 |
|  73724 |  5 | 24 |
|  90107 |  6 |  7 |
| 106490 |  7 | 90 |
| 122873 |  8 | 73 |
| 139256 |  9 | 56 |
| 155639 | 10 | 39 |
+--------+----+----+
10 rows in set (0.10 sec)

--------------
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
--------------

+--------+----+----+
| pk     | j  | k  |
+--------+----+----+
|   8192 |  1 | 92 |
|  24575 |  2 | 75 |
|  40958 |  3 | 58 |
|  57341 |  4 | 41 |
|  73724 |  5 | 24 |
|  90107 |  6 |  7 |
| 106490 |  7 | 90 |
| 122873 |  8 | 73 |
| 139256 |  9 | 56 |
| 155639 | 10 | 39 |
+--------+----+----+
10 rows in set, 2 warnings (0.11 sec)

Bye

# Query_time: 0.000252  Lock_time: 0.000123 Rows_sent: 10  Rows_examined: 10
SELECT max(pk), j FROM tq GROUP BY j;

# Query_time: 0.000334  Lock_time: 0.000122 Rows_sent: 10  Rows_examined: 20
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;

# Query_time: 134.116634  Lock_time: 0.000057 Rows_sent: 10  Rows_examined: 671170560
SELECT pk, j, k FROM  tq t1
WHERE  pk=(SELECT MAX(t2.pk) FROM tq t2 WHERE t1.j = t2.j);

# Query_time: 1.061884  Lock_time: 0.000076 Rows_sent: 10  Rows_examined: 245750
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;

# Query_time: 0.108313  Lock_time: 0.000125 Rows_sent: 10  Rows_examined: 10
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;

# Query_time: 0.108831  Lock_time: 0.000153 Rows_sent: 10  Rows_examined: 10
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;