John Davidson

Message:

I'm using Laravel 8. Want to fetch from table unique rows (based on name) having highest amount for each name, to be ordered by amount descending.

| Name | Amount |

| John |   200  |

| Mane |   100  |

| Zoey |   300  |

| Mane |   300  |

| Zoey |   500  |

Result Needed :

| Name | Amount |

| Zoey |   500  |

| Mane |   300  |

| John |   200  |

Solution in query builder or eloquent both are fine. Please keep in mind that server has ONLY_FULL_GROUP_BY rule enforced.

---

My Answer:
You can achieve this by using a subquery to get the maximum amount for each name and then joining it back to the original table to fetch the corresponding row. Here's how you can do it using Laravel's query builder:

php
$uniqueRows = DB::table('your_table_name')
->select('name', 'amount')
->join(DB::raw('(SELECT name, MAX(amount) as max_amount FROM your_table_name GROUP BY name) as sub'), function($join) {
$join->on('your_table_name.name', '=', 'sub.name');
$join->on('your_table_name.amount', '=', 'sub.max_amount');
})
->orderBy('amount', 'desc')
->get();

dd($uniqueRows);


This query first creates a subquery to get the maximum amount for each name. Then, it joins this subquery back to the original table based on the name and amount columns. Finally, it orders the result by amount in descending order.

Make sure to replace 'your_table_name' with the actual name of your table in the query.