I have a database that looks like this:
ID | facebook_id | likes | created_at
--------------------------------------------------
1 | x | 100 | 2017-01-01 12:10:15
2 | x | 110 | 2017-01-02 12:10:15
3 | y | 115 | 2017-01-01 12:10:15
4 | y | 120 | 2017-01-02 12:10:15
5 | z | 200 | 2017-01-01 12:10:15
6 | z | 201 | 2017-01-01 12:10:15
7 | z | 200 | 2017-02-02 12:10:15
8 | z | 205 | 2017-02-03 12:10:15
9 | z | 250 | 2017-03-04 12:10:15
I want to get the latest record for each of the unique facebook_id
's in the table then order it by likes
in descending
order. I am using laravel 5.4 and mysql for the database. In teradata I would do something like
QUALIFY ROW_NUMBER() OVER(PARTITION BY facebook_id ORDER BY created_at DESC) = 1
to order the results by facebook_id
and get the latest row by unique facebook_id. But I'm not sure how to do this in laravel/mysql.
I have tried
Database::select('facebook_id', 'likes', 'created_at')
->orderBy('likes', 'desc')
->distinct('facebook_id')
->latest('created_at')
->limit(20)
->get();
But it just orders it by likes
and doesnt get me unique facebook_id
's
My results are:
Top Profile ID: z Likes:250
Top Profile ID: z Likes:205
Top Profile ID: z Likes:201
Top Profile ID: z Likes:200
What I want to get is:
Top Profile ID: z Likes:250
Top Profile ID: y Likes:120
Top Profile ID: x Likes:110
via kevinabraham