SQL – joining 3 tables and choosing newest logged entry per id

I got rather complicated riddle to solve. So far I’m unlocky.

I got 3 tables which I need to join to get the result.

Most important is that I need highest h_id per p_id. h_id is uniqe entry in log history. And I need newest one for given point (p_id -> num).

Apart from that I need ext and name as well.

history

+----------------+---------+--------+
|     h_id       |  p_id   | str_id |
+----------------+---------+--------+
|     1          | 1       |  11    |
|     2          | 5       |  15    |
|     3          | 5       |  23    |
|     4          | 1       |  62    |
+----------------+---------+--------+

point

+----------------+---------+
|     p_id       |  num    | 
+----------------+---------+
|     1          | 4564    | 
|     5          | 3453    |
+----------------+---------+

street

+----------------+---------+-------------+
|     str_id     |  ext    |     name    |
+----------------+---------+-------------+
|     15         |         | Mein st. 33 | - bad name
|     11         |         | eck  st. 42 | - bad name
|     62         | abc     | Main st. 33 |
|     23         | efg     | Back st. 42 |
+----------------+---------+-------------+

EXPECTED RESULT

+----------------+---------+-------------+-----+
|     num        |  ext    |     name    |h_id |
+----------------+---------+-------------+-----+
|     3453       | efg     | Back st. 42 | 3   |
|     4564       | abc     | Main st. 33 | 4   |
+----------------+---------+-------------+-----+

I’m using Oracle SQL. Tried using query below but result is not true.

SELECT num, max(name), max(ext), MAX(h_id) maxm FROM history
INNER JOIN street on street.str_id = history._str_id
INNER JOIN point on point.p_id = history.p_id
GROUP BY point.num

​x
 
SELECT num, max(name), max(ext), MAX(h_id) maxm FROM historyINNER JOIN street on street.str_id = history._str_idINNER JOIN point on point.p_id = history.p_idGROUP BY point.num​

Answer

In Oracle, you can use keep:

SELECT p.num, 
       MAX(h.h_id) as maxm,
       MAX(s.name) KEEP (DENSE_RANK FIRST ORDER BY h.h_id DESC) as name,
       MAX(s.ext) KEEP (DENSE_RANK FIRST ORDER BY h.h_id DESC) as ext
FROM history h INNER JOIN
     street s
     ON s.str_id = h._str_id INNER JOIN
     point p
     ON p.p_id = h.p_id
GROUP BY p.num;

 
SELECT p.num,        MAX(h.h_id) as maxm,       MAX(s.name) KEEP (DENSE_RANK FIRST ORDER BY h.h_id DESC) as name,       MAX(s.ext) KEEP (DENSE_RANK FIRST ORDER BY h.h_id DESC) as extFROM history h INNER JOIN     street s     ON s.str_id = h._str_id INNER JOIN     point p     ON p.p_id = h.p_idGROUP BY p.num;​

The keep syntax allows you to do “first()” and “last()” for aggregations.

Advertisement

Answer