How can i get the latest record in database based on datetime?

Are you using laravels timestamps (created_at, updated_at)? If so, there's a handy eloquent/qb method called ->latest() which will give you that.

If not, you'd have to ->order_by('datetime', 'desc')

And if you only want the single most recent

->order_by('datetime', 'desc')
->limit(1);

Hi, using SQL (MySQL) you can do it like this:

SELECT * 
FROM table
WHERE id IN (SELECT id FROM table WHERE datetime = (SELECT MAX(datetime) FROM table))
ORDER BY id DESC
LIMIT 1

and using Laravel eloquent ORM:

$results = Table::whereIn('id', function($query){
            $query->select('id')->where('datetime', DB::raw("(select max('datetime') from table)"));
})->orderBy('id', 'desc')->first();

I first take all the rows with the most recent datetime, and then take the last one inserted, using id as the autoincrement column.

Hope it helps!

@Cronix Instead of using ->get() i just changed it to ->latest()->get()?

i am using

->groupby('name')
->order_by('datetime', 'desc')

But it seem like not displaying the latest record.

Instead of ->get() you would use ->latest()->first() which orders the results and returns back a single record (model)

But this latest thing assumes its using the created_at column but you can override this;

$results = Table::latest('datetime')->first();

(but seriously, your model is called Table and your column is called datetime) ?? WTF?

@Snapey Well, it's just a sample,

I want it to be like this Before:

    id  branch_id  name    qty     datetime
    --- ---------  ----    ----    -----------
    1   1           a      5       2017-07-28 12:26:01
    2   2           b      3       2017-07-28 12:26:02
    3   3           c      4       2017-07-28 12:26:03
    4   1           a      2       2017-07-29 12:26:04
    5   2           b      2       2017-07-29 12:26:05
    6   3           c      2       2017-07-29 12:26:06

After

 id  branch_id  name    qty     datetime
    --- ---------  ----    ----    -----------
    4   1           a      2       2017-07-29 12:26:04

http://sqlfiddle.com/#!9/71f834/5 , i want to show the latest record but cannot make it.

Your example is not the latest record?

Do you mean the latest for branch 1 ?

Is your datetime column a datetime datatype?

If you want the latest for branch_id of 1, you'd just add an additional ->where().

Model::where('branch_id', 1)
    ->latest('datetime')
    ->first();

@Snapey Yes, i mean latest by branch 1 if login as branch 1.

So what is wrong with what @cronix posted?

I think the problem is that, could have same datetime for several records with same branch_id, but different PrimaryKey id, that's why I proposed at the beggining of the thread to find the most recent date and then order by PK (id) and take the first row.

The where with the branch_id could be added as extra filter.

I don't know if the datetime could change some time, in this case, the solution should be different, of course.

Also, @dreamxyz in your sqlfiddle, I think the SQL query should be:

SELECT A.* 
FROM A as A 
LEFT JOIN A as B on A.id = B.id
GROUP BY name
ORDER BY A.datetime desc
LIMIT 1

Try the code below

$id=2 //define the number of record to fetch out
$results = Table::orderBy('datetime', 'desc')->limit($id)->get();

It will help you sort the datetme in descending order, get the latest number of records you've defined out of the result sets

@abdul29 this question is 2 years old!

@CRAZYLIFE - * Instead of using ->get() i just changed it to ->latest()->get()?*

Yes that should work.

@NANADJEI2 - latest()->first() works as well.

@ognjen, @nanadjei2 whats up with you guys? This thing was done two years ago, and you're not even answering the question. Try reading the thread. In particular, the date it was posted

Ditto

hello @snapey so i just can't done with this, $latestdata = DB::table('items') ->join('detail_items', 'items.item_id', '=', 'detail_items.item_id') ->select('items.', 'detail_items.') ->orderBy('detail_items.created_at', 'desc') ->get(); // dd($latestdata); return view('item.index', ['latestdata' => $latestdata]);

i'm trying to get the last value from the row but there are multiple value showing instead.

@bintangjtobing start your own question, This one is 4 years old

Thanks, it works...

If you are using ->latest()->get() then it will get records base on created_at, if we get records base on created_at then how about order by id desc both work as same. if you need by other column datetime then you can use ->orderBy('columnName','desc/asc')->get() otherwise you can use latest() function.

You can use as @snapey replied.

I think are you creazy

@wiznox dude, did you even read the question?

ok @snapey :)