SQL query to get last record based on table relation

Question

i have 3 tables boxes, stones and papers; each box is related to a paper through a stone so my goal is to get the last box for each paper (boxes can share papers). have tried an ActiveRecord way to go in rails but could not use an aggregate function in a uuid coulmn so this does not work:

Box.joins(:stone).group('stone.paper_id').maximum(:id).values

im struggling going for a pure SQL statement since missing the stones table, i have the following:

select distinct on (papers.id) boxes.created_at, boxes.id 
from papers 
left join boxes on paper.id = boxes.id 
order by paper.id, boxes.created_at DESC;

all 3 tables primary keys are uuids together with other columns that are not relevant for the query since i just want as a return the uuids of the last box for each paper.

table:boxes
+--------------------------------------+-----------------+-----------+---------------------------------+--------------------------------------+
|                  id                  |      email      | delivered |           created_at            |               stone_id               |
+--------------------------------------+-----------------+-----------+---------------------------------+--------------------------------------+
| 61a341b0-a147-4534-9368-fdbc7b61fc0c | test@test.com   | true      | Fri, 04 Mar 2022 00:19:31 +0000 | 7fda6668-e9b2-45b3-957a-fbdbcd833cd0 |
| c20f4b61-8606-4aa7-870a-29b9df9d9492 | test2@test.com  | true      | Thu, 24 Feb 2022 11:42:01 +0000 | cdb35b8a-b553-4095-8b14-e855ebdf5044 |
| 9202384f-1895-4f94-9972-3ef837655aae | test3@test.com  | false     | Thu, 10 Mar 2022 00:59:54 +0000 | bbd5dcbc-b38d-4751-aaac-2b3dd83c5545 |
+--------------------------------------+-----------------+-----------+---------------------------------+--------------------------------------+

table:stones
+--------------------------------------+--------+------+---------------------------------+--------------------------------------+
|                  id                  | status | code |           created_at            |               paper_id               |
+--------------------------------------+--------+------+---------------------------------+--------------------------------------+
| 7fda6668-e9b2-45b3-957a-fbdbcd833cd0 |      1 |    3 | Sun, 06 Mar 2022 12:58:56 +0000 | a0acba15-e321-4f9f-996f-a6c16e56300d |
| cdb35b8a-b553-4095-8b14-e855ebdf5044 |      1 |    4 | Thu, 03 Mar 2022 19:57:14 +0000 | a0acba15-e321-4f9f-996f-a6c16e56300d |
| bbd5dcbc-b38d-4751-aaac-2b3dd83c5545 |      2 |    5 | Fri, 11 Mar 2022 11:50:08 +0000 | de936cf2-c158-4961-9ef4-60affc4ff87f |
+--------------------------------------+--------+------+---------------------------------+--------------------------------------+

table:papers
+--------------------------------------+------+---------------------------------+
|                  id                  | type |           created_at            |
+--------------------------------------+------+---------------------------------+
| a0acba15-e321-4f9f-996f-a6c16e56300d |    1 | Sat, 05 Mar 2022 05:59:00 +0000 |
| de936cf2-c158-4961-9ef4-60affc4ff87f |    5 | Thu, 03 Mar 2022 19:57:14 +0000 |
| 473a9dd4-3f38-49d0-8d1e-b5ab87e8ea92 |    4 | Sat, 12 Mar 2022 22:55:16 +0000 |
+--------------------------------------+------+---------------------------------+

box1 has a stone1 that relates to paper1
box2 has a stone2 that relates to paper1
box3 has a stone3 that relates to paper2

as a result i want box1 and box3 uuids since they are the most recent boxes (order by the time they were created) for each available paper, box2 is ignored since shares paper1 with box1 and it is not the most recent one. any help will be much appreciated! thanks in advance!

result:most recent boxes ids
+--------------------------------------+
|                  id                  |
+--------------------------------------+
| 61a341b0-a147-4534-9368-fdbc7b61fc0c |
| 9202384f-1895-4f94-9972-3ef837655aae |
+--------------------------------------+

Answer 1

you can do this query (Result here)

with x as (
select row_number() over (partition by p.id order by b.created_at desc) as rn,b.id as id_box,p.id as id_paper
from boxes b join stones s on b.stone_id = s.id
join papers p on p.id = s.paper_id)
select x.id_box from x where rn = 1

Answer 2

The cte finds the latest id based on paper_id wich is related to papers table . And then join with the box table.

Try and let me know if it helps:

with cte as 
    (
      select s.id,
             s.paper_id,row_number() over(partition by paper_id order by created_at desc ) as rn
      from stones s
    ) select b.id
      from boxes b 
      inner join cte c on c.id=b.stone_id
      inner join papers p on c.paper_id=p.id
      where c.rn=1
      order by b.created_at desc;

Result:

id
9202384f-1895-4f94-9972-3ef837655aae
61a341b0-a147-4534-9368-fdbc7b61fc0c

Demo

Answer 3

Try this :

SELECT DISTINCT ON (p.id)
       b.id
  FROM boxes AS b
 INNER JOIN stones AS s
    ON s.id = b.stone_id
 INNER JOIN papers AS p
    ON p.id = s.paper_id
 ORDER BY p.id, b.created_at DESC

see the test result in dbfiddle.

Answer 4

"SQL query to get last record based on table relation"

There is no record in table, just logical rows grouped into page in any order. There is no default or specific order like a spreadsheet into a table page and rows are placed into the page as a heap. You must well understood that you will never ever have any way to find the last "record" !

In a database, you will find, only what you will store into. If you want to find the last row that you have inserted, you need to provide this information at the INSERT statement.

And I say YOU, because any other user can do the same inserts at the same time, with the same values... This is concurrency !

Now the question is "what do you functionnaly want ?"