Ad

How To Get Latest Records Basis On Timestamp But Not Duplicate From A Table In Laravel?

- 1 answer

i have a table

quotations

id  lead_id    subject           created_at
1      2      Quotation 1    2019-09-03 17:07:17
2      2      Quotation 2    2019-09-04 17:10:30
3      2      Quotation 3    2019-09-05 17:20:57
4      4      Quotation 4    2019-09-06 20:00:21
5      4      Quotation 5    2019-09-07 20:57:17

and i need result like this:

id  lead_id    subject           created_at
3      2      Quotation 3    2019-09-05 17:20:57
4      4      Quotation 5    2019-09-07 20:57:17

means records which are latest and lead_id is not duplicated. so far i can get lead_id and created_at columns with this query but i want to get complete row.:

$quotations = DB::table('quotations')
            ->select('lead_id', DB::raw('MAX(created_at) as created_at'))
            ->groupBy('lead_id')
            ->get();
Ad

Answer

You can use sub-query concept. Attach the above query as tail query and add another main query to select all using where statement.

$quotations = DB::table('quotations')
        ->select('lead_id', DB::raw('MAX(created_at) as created_at'))
        ->groupBy('lead_id')
        ->get();

 $q = DB:table ('quotations')
->where ('lead_id' , $quotations->lead_id)
->where ('created_at' , $quotations->created_at);
Ad
source: stackoverflow.com
Ad