Newest record when multiple found

Question

I have these project records:

---------------------------------
|id|name      |rate  |start_date|
---------------------------------
|1 |project A |90.0  |01-01-2017|
|2 |project B |100.0 |01-01-2018|
|3 |project A |95.0  |01-01-2018|
---------------------------------

I want all records, except when there are multiple records with the same name, in which case I only want the one with the most recent start_date. In the example above, I want to get records 2 and 3.

How can I do that? So far I tried with distinct, but without success.

Tried `distinct` with `order(start_date: :desc)`? – Jagdeep Singh Jul 26 '18 at 10:54 — Jagdeep Singh, Jul 26 '18 at 10:54

score 0 · Answer 1 · answered Jul 26 '18 at 09:47

0

Something like this might work:

Project.all.group([:name, :id]).select("id, name, max(start_date)")

But if the projects are indeed the same, you might want to rethink the design. What is the reason for the duplication of records?

answered Jul 26 '18 at 09:47

bo-oz

2,842
2
24
44

The reason for this is that a project might get a new rate (I updated the table above) and I need the most actual rate to calculate turnovers. – John Jul 26 '18 at 10:02
In that case, you can better add a flag that indicates that the rate is no longer valid. Or even better, create separate ProjectRate model, that holds the rates for the associated Projects. – bo-oz Jul 26 '18 at 10:08
Hmm, a flag sounds good, might do that to make this query easier. – John Jul 26 '18 at 10:10
1

But please consider creating a separate model for this. Makes your life a lot easier. It even allows you to do per project calculations about changes in rates, average rates etc. A lot more fun! – bo-oz Jul 26 '18 at 10:12

score 0 · Answer 2 · answered Jul 26 '18 at 12:55

The Query:

SELECT project.*
FROM project
LEFT JOIN project as p 
ON 
  p.name = project.name 
  AND 
  project.start_date < p.start_date
WHERE p.start_date IS NULL

HOW DOES THAT WORK! (I've been there)

You can refer to my answer here or here to understand how it works.

Wait but how to do it in rails ?!

Project.joins(%Q|
  LEFT JOIN projects as p 
  ON 
  p.name = projects.name 
  AND 
  projects.start_date < p.start_date
|).where(p: { start_date: nil })

Newest record when multiple found

2 Answers2