Get latest record using multiple column values

Question

I have a table that stores user purchase history:

Table Purchases : id, user, brand, model, color, date

I'd like to get a list of latest purchases made by a user, grouped by brand and model.

Here is an example dataset:

1, Bob,  Apple,   iPhone 6, Gold,   2017-01-03
2, Bob,  Apple,   iPhone 6, Silver, 2017-01-09
3, Bob,  Apple,   iPhone 6, Gold,   2017-01-18
4, Bob,  Samsung, Galaxy,   Black,  2017-01-22
5, Bob,  Samsung, Galaxy,   Black,  2017-01-28
6, Jane, Apple,   iPhone 6, Silver, 2017-01-18
7, Jane, Apple,   iPhone 6, Silver, 2017-01-22
8, Jane, Samsung, Galaxy,   White,  2017-01-28

I'd like to retrieve display a list like so:

2, Bob,  Apple,   iPhone 6, Silver, 2017-01-09
3, Bob,  Apple,   iPhone 6, Gold,   2017-01-18
7, Jane, Apple,   iPhone 6, Silver, 2017-01-22
8, Jane, Samsung, Galaxy,   White,  2017-01-28

So that I can see the latest date of purchase for each variation of a phone.

I tried GROUP BY brand, model, color, but it doesn't return the latest row as ordering before group is not supported(?).

Please let me know if I can clear anything up. Thanks in advance.

Does this answer your question? [MySQL order by before group by](https://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by) — mickmackusa, Feb 14 '23 at 19:55

score -1 · Answer 1 · answered Nov 09 '17 at 00:20

-1

You can use the laravel query builder

$purchases = DB::table('Purchases')
            ->groupBy('brand', 'model', 'color')
            ->orderBy('date', 'desc')
            ->get();

answered Nov 09 '17 at 00:20

Nisarg Shah

282
2
6
14

score -1 · Answer 2 · edited Feb 14 '23 at 19:51

-1

Try this

$date = array();
foreach ($purchase as $key => $row)
{
    $date[$key] = $row['date'];
}
array_multisort($date, SORT_ASC, $purchase);

edited Feb 14 '23 at 19:51

mickmackusa

43,625
12
83
136

answered Nov 09 '17 at 00:38

Olasunkanmi

323
6
18

If this was the correct resolving advice, it would have been more appropriate to close this question as a duplicate instead of answering. [The fundamental goal of closing duplicate questions is to help people find the right answer by getting all of those answers in one place.](https://stackoverflow.com/help/duplicates#:~:text=The%20fundamental%20goal%20of%20closing%20duplicate%20questions%20is%20to%20help%20people%20find%20the%20right%20answer%20by%20getting%20all%20of%20those%20answers%20in%20one%20place.) However, this answer only sorts by one column, but the requirement is different. – mickmackusa Feb 14 '23 at 19:52

Get latest record using multiple column values

2 Answers2