How to Get Newest Data with `created_at` Column

Question

How can I query the data where id_konsul = 4, and how can I get the latest staflow from created_at if it is the same?

example

id  id_konsul   staflow created_at

1   4   1   21/05/2018 11.03
2   4   2   22/05/2018 11.03
3   4   3   23/05/2018 11.03
4   4   4   24/05/2018 11.03
5   4   5   25/05/2018 11.03
6   4   6   26/05/2018 11.03
7   4   7   27/05/2018 11.03
8   4   6   28/05/2018 11.03
9   4   7   29/05/2018 11.03
10  4   7   30/05/2018 11.03
11  4   8   31/05/2018 11.03

than i get this

id  id_konsul   staflow created_at
1   4   1   21/05/2018 11.03
2   4   2   22/05/2018 11.03
3   4   3   23/05/2018 11.03
4   4   4   24/05/2018 11.03
5   4   5   25/05/2018 11.03
8   4   6   28/05/2018 11.03
10  4   7   30/05/2018 11.03
11  4   8   31/05/2018 11.03

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, May 25 '18 at 05:59

score 1 · Answer 1 · edited May 25 '18 at 05:37

1

try this query

SELECT * FROM test1 n 
WHERE created_at=(SELECT MAX(created_at)FROM test1 
  WHERE staflow=n.staflow) 
order by id

edited May 25 '18 at 05:37

SherylHohman

16,580
17
88
94

answered May 25 '18 at 05:15

Vipul

896
7
14

Tariq Rasheed · Answer 2 · 2018-05-25T04:50:36.110

0

You can user INNER JOIN.

Example:

SELECT t0.* from {TABLE_NAME} AS t0 INNER JOIN {TABLE_NAME} t1 ON t0.staflow = t1.staflow WHERE t0.created_at > t1.created_at and t0.id_konsul = 4

edited May 25 '18 at 04:50

answered May 25 '18 at 04:02

Tariq Rasheed

158
7

i want to get all of staflow but newest form created_at – WM_ May 25 '18 at 04:32

score 0 · Answer 3 · answered May 25 '18 at 04:04

0

Please try this query.

SELECT * FROM table_name WHERE id_konsul = 4 ORDER BY created_at DESC LIMIT 1;

answered May 25 '18 at 04:04

Gufran Hasan

8,910
7
38
51

score 0 · Accepted Answer · answered May 25 '18 at 05:02

To get latest row per staflow attribute you can use a self join

select a.*
from demo a
join (
    select id_konsul,staflow, max(created_at) created_at
    from demo
    where id_konsul = 4
    group by id_konsul, staflow
) b on a.staflow = b.staflow
    and a.id_konsul = b.id_konsul 
    and a.created_at = b.created_at;

Or using a left join

select a.*
from demo a
left join demo b on a.id_konsul = b.id_konsul
                and a.staflow = b.staflow 
                and a.created_at < b.created_at
where a.id_konsul = 4 
and b.id_konsul is null

Demo

To write above queries using laravel's query builder you can use following references

Laravel Eloquent select all rows with max created_at

Laravel - Get the last entry of each UID type

Laravel Eloquent group by most recent record