CODEWITHSUNDEEP
phpmysqlsqllaravel
TryHardz
TryHardz

Reputation: 159

How to get last data group by each id

How can I get last date data from each user_id :

User

id |  name   |
1  |  name 1 |
2  |  name 2 |
3  |  name 3 |

Date

id |  date       |  user_id  |
1  |  2018-08-13 |    1      |
2  |  2018-08-13 |    2      |
3  |  2018-08-15 |    1      |
4  |  2018-08-14 |    2      |

How can I output like this :

user_id |  date       |  name
    1   |  2018-08-15 |  name 1
    2   |  2018-08-14 |  name 2
    3   |  null       |  name 3

UPDATE

I want my output to select date table join by user table -> and get the last date from each user_id If my user_id doesn't have in date value it will output 0 or null

Upvotes: 2

Views: 81

Answers (4)

Zakaria Acharki
Zakaria Acharki

Reputation: 67505

Since you're tagging Laravel I suggest to you using the Query Builder like :

DB::table('dates')
    ->join('users', 'dates.user_id', '=', 'users.id')
    ->select(db::raw('users.name, user_id, max(date)'))
    ->groupBy('user_id','name')
    ->get();

OUTPUT:

Illuminate\Support\Collection {#2957
     all: [
       {#2944
         +"name": "name 1",
         +"user_id": 1,
         +"max": "2018-08-15",
       },
       {#2941
         +"name": "name 2",
         +"user_id": 2,
         +"max": "2018-08-14",
       },
     ],
   }

Upvotes: 2

A Redfearn
A Redfearn

Reputation: 528

You can use MAX 

SELECT u.UserID, MAX(d.date) date, u.name
FROM User u
left join Date d on u.id = d.id

Upvotes: 1

Hetal
Hetal

Reputation: 164

SELECT u.id,MAX(d.date),u.name FROM DATE d JOIN user u ON d.user_id = u.id GROUP BY d.user_id

Upvotes: 1

Devart
Devart

Reputation: 121912

For getting just maximum date for user, you can use this one -

SELECT
  u.id,
  u.name,
  MAX(d.date) date
FROM user u
  LEFT JOIN date d
    ON u.id = d.user_id
GROUP BY u.id

1   name 1  15-Aug-18
2   name 2  14-Aug-18
3   name 3  (null)

Upvotes: 1

Related Questions