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 | [email protected] | true | Fri, 04 Mar 2022 00:19:31 +0000 | 7fda6668-e9b2-45b3-957a-fbdbcd833cd0 |
| c20f4b61-8606-4aa7-870a-29b9df9d9492 | [email protected] | true | Thu, 24 Feb 2022 11:42:01 +0000 | cdb35b8a-b553-4095-8b14-e855ebdf5044 |
| 9202384f-1895-4f94-9972-3ef837655aae | [email protected] | 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 |
+--------------------------------------+
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