0

I have DB table urls similar to this

id | user_id | name
----------------------------
1  | 56      | John
2  | 25      | Carry
3  | 56      | Jim
4  | 12      | Jolana
5  | 12      | Ava

What I need to do is to select the rows with the first occurance of user_id ordered in DESC by id. In this case it would mean to select rows with IDs 5, 3 and 2.

Can this be achieved somehow using Eloquent, or if not, pure SQL? I can imagine writing a script for this but I would like to make it work with one query.

Jakub Szymsza
  • 86
  • 9
  • what you have tried? do you have a model or you want to go into [raw sql](https://laravel.com/docs/master/database) (that i personally wont endorse like the two answer below). if you had a model named `Url`, roughly, you can call the following `Url:all()->orderByDesc('id')->groupBy('user_id')`. do note however about this [config perks](https://stackoverflow.com/q/41571271/4648586). – Bagus Tesa May 05 '18 at 11:30

2 Answers2

1

This is easy to do with SQL:

select t.*
from t
where t.id = (select min(t2.id) from t t2 where t2.user_id = t.user_id);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I don't really understand the query, could you please adjust it to fit my example with table name `urls`? – Jakub Szymsza May 05 '18 at 11:04
  • @JakubSzymsza Here you go [Demo](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4b417eefaa39679ab9020d2684577784). I guess you need MAX not MIN. – Lukasz Szozda May 05 '18 at 11:19
0

You could use ROW_NUMBER:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY id DESC) AS rn
      FROM tab_name) sub
WHERE rn = 1
ORDER BY id DESC;

DBFiddle Demo - MariaDB 10.2

DBFiddle Demo - MySQL 8.0

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275