Distinct most recent data from database

Question

I am storing data in my database. The data being stored looks like this

id  | upload_month | created_at
-----------------------------------------
1   | January      | 2017-01-30 13:22:39
-----------------------------------------
2   | Febuary      | 2017-01-30 13:23:42
-----------------------------------------
3   | January      | 2017-01-30 13:25:33

Within my Controller I am trying to retrieve the distinct upload_month, but get the latest inserted version for each. At the moment I am trying

$uploadedFile = UploadedFile::groupBy('upload_month')->orderBy('created_at', 'desc')->get();

The problem is that this is returning the following

id  | upload_month | created_at
-----------------------------------------
1   | January      | 2017-01-30 13:22:39
-----------------------------------------
2   | Febuary      | 2017-01-30 13:23:42
-----------------------------------------

So for the January record it is giving the older version. If I change it to ->orderBy('created_at', 'asc') it returns the same records but Febuary being the first row.

In essense, what I am after is this

id  | upload_month | created_at
-----------------------------------------
1   | January      | 2017-01-30 13:25:33
-----------------------------------------
2   | Febuary      | 2017-01-30 13:23:42
-----------------------------------------

How am I able to achieve this?

Thanks

https://laravel.com/docs/5.4/queries#selects try the distinct method https://laravel.com/api/5.4/Illuminate/Database/Query/Builder.html#method_distinct ;) — Rene M., Jan 30 '17 at 14:48
The only reason I was not using distinct was because it is doing a raw DB query rather than using elequent — katie hudson, Jan 30 '17 at 14:50

score 4 · Accepted Answer · answered Jan 30 '17 at 16:13

You should GROUP BY all fields you want to select, no only one. This article explain the issue: https://www.psce.com/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/

The proper SQL query in this case would be:

SELECT id, upload_month, created_at
  FROM uplodaded_file
  JOIN (SELECT upload_month, MAX(created_at) created_at
          FROM uplodaded_file
      GROUP BY upload_month) months
    ON upload_month = months.upload_month
   AND created_at = months.created_at

The eloquent version of this is a little bit tricky. It will be better to use a raw query in this case.

score 0 · Answer 2 · answered Jan 31 '17 at 09:20

0

You should use the latest() method instead of orderBy:

UploadedFile::latest()->distinct()->get();

answered Jan 31 '17 at 09:20

Haider Ali

1,081
8
23

Unfortunatly that does not return the unique months. I am being returned all 3 results. – katie hudson Jan 31 '17 at 13:02
try groupBy('upload_month') with latest. – Haider Ali Jan 31 '17 at 15:40
Now it returns two months, but not the latest January one – katie hudson Jan 31 '17 at 15:53
1

try this UploadedFile::groupBy('upload_month')->latest()->distinct()->get(); – Haider Ali Jan 31 '17 at 16:35

score 0 · Answer 3 · answered Jul 22 '21 at 07:22

0

I faced this issue and solved it this way.

UploadedFile::select(DB::raw('upload_month, MAX(created_at) as latest_date'))
->groupBy('upload_month')->orderBy('latest_date', 'desc')->get()

answered Jul 22 '21 at 07:22

CZ Tang Choo Zin

9
2

Please provide a detailed explanation to your answer, in order for the next user to understand your answer better. – Elydasian Jul 23 '21 at 12:13

Distinct most recent data from database

3 Answers3

Linked