Let's say i have a user table like this :
+----+-----------+----------------------+------+
| ID | Name | Email | Age |
+----+-----------+----------------------+------+
| 1 | John | john.doe1@mail.com | 24 |
| 2 | Josh | josh99@mail.com | 29 |
| 3 | Joseph | joseph410@mail.com | 21 |
| 4 | George | gge.48@mail.com | 28 |
| 5 | Joseph | jh.city89@mail.com | 24 |
| 6 | Kim | kimsd@mail.com | 32 |
| 7 | Bob | bob.s@mail.com | 38 |
| 8 | Joseph | psa.jos@mail.com | 34 |
| 9 | Joseph | joseph.la@mail.com | 28 |
| 10 | Jonathan | jonhan@mail.com | 22 |
+----+-----------+---------+------------+------+
In the actual, it have more data and some of them is duplicated with more than two record, but the point is i want to get only the first and the second row in duplicated rows that have name "Joseph", but how to do it ? My code this far...
User::withTrashed()->groupBy('name')->havingRaw('count("name") >= 1')->get();
With that code the result will retrieve :
+----+-----------+----------------------+------+
| ID | Name | Email | Age |
+----+-----------+----------------------+------+
| 1 | John | john.doe1@mail.com | 24 |
| 2 | Josh | josh99@mail.com | 29 |
| 3 | Joseph | joseph410@mail.com | 21 |
| 4 | George | gge.48@mail.com | 28 |
| 6 | Kim | kimsd@mail.com | 32 |
| 7 | Bob | bob.s@mail.com | 38 |
| 10 | Jonathan | jonhan@mail.com | 22 |
+----+-----------+---------+------------+------+
And i use this code to try to get the second duplicate row :
User::withTrashed()->groupBy('name')->havingRaw('count("name") >= 2')->get();
The result still same as the mentioned above :
+----+-----------+----------------------+------+
| ID | Name | Email | Age |
+----+-----------+----------------------+------+
| 1 | John | john.doe1@mail.com | 24 |
| 2 | Josh | josh99@mail.com | 29 |
| 3 | Joseph | joseph410@mail.com | 21 |
| 4 | George | gge.48@mail.com | 28 |
| 6 | Kim | kimsd@mail.com | 32 |
| 7 | Bob | bob.s@mail.com | 38 |
| 10 | Jonathan | jonhan@mail.com | 22 |
+----+-----------+---------+------------+------+
I want the result is to get record that have the id "5" with name "Joseph" like this :
+----+-----------+----------------------+------+
| ID | Name | Email | Age |
+----+-----------+----------------------+------+
| 1 | John | john.doe1@mail.com | 24 |
| 2 | Josh | josh99@mail.com | 29 |
| 4 | George | gge.48@mail.com | 28 |
| 5 | Joseph | jh.city89@mail.com | 24 |
| 6 | Kim | kimsd@mail.com | 32 |
| 7 | Bob | bob.s@mail.com | 38 |
| 10 | Jonathan | jonhan@mail.com | 22 |
+----+-----------+---------+------------+------+
But it seems only the first duplicate row is retrieved and i can't get the second duplicated row, can anybody give me suggestion ?
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire