Recent user requests

Question

I have 2 tables, for example, let's say this is data:

user table:

------------------------------ | user_id | username | ------------------------------ | 1 | danny | | 2 | george | | 3 | lana | | 4 | kim | | 5 | tim | ------------------------------ 

users_logins table:

  ----------------------------------------------- | record_id | user_id | recorder | ----------------------------------------------- | 1 | 3 | 2012-11-06 04:18:26 | | 2 | 3 | 2012-11-06 04:31:05 | | 3 | 2 | 2012-11-06 03:44:22 | | 4 | 1 | 2012-11-06 04:18:58 | | 5 | 1 | 2012-11-06 04:30:15 | | 6 | 3 | 2012-11-06 04:31:05 | X | 7 | 1 | 2012-11-06 05:53:47 | | 8 | 1 | 2012-11-06 05:55:15 | X | 9 | 4 | 2012-11-06 05:59:31 | | 10 | 4 | 2012-11-06 06:12:55 | X ----------------------------------------------- 

I want to show the last 3 registered users, so as a result only lines marked with an X or words with unique recent entries will be shown.

What does the request look like?

Answer 1

I think this is what you want.

 var resentLogedInUsers = users .Join( users_logins, u => u.user_id, l => l.user_id, (u, l) => new { User = u, Login = l.recorder }) .GroupBy(j => j.User) .Select(l => new {User = l.Key, LastLogin = l.Max(d => d.Login)}) .OrderByDescending(r => r.LastLogin) .Take(3); 

Answer 2

Try this for linq for objects

  (from login in users_logins.OrderByDescending(user => user.recorder) from user in users where user.user_id == login.user_id select user).Distinct().Take(3) 

This request
1. first sort by date
2. Then combines the sorted login data with user data,
3. then take individual users
4. and then finally take the first 3 entries.

---

Below is an alternative query

 from login in users_logins.OrderByDescending(user =>user.recorder).GroupBy(user=>user.recorder).SelectMany( users=>users.First()).Take(3) from user in users where user.user_id == login.user_id select user 

This request
1. first grades
2. then groups on user_id
3. then take the first 3 entries with a different user id,
4. Then connects to user data.