Last login joining two tables

Question

I have two tables:

members (
  userid int primary key
)

login_history (
  userid int,
  login datetime
)

An entry to login_history table is made every time a user logs in.

I'm trying to write a query to find every users last login time. ie. The MAX(login) or return null if the user has no login history. What's the best SQL to accomplish this? thanks

CheeseConQueso · Answer 1 · 2011-01-06T19:10:52.050

3

try this

select nvl((max(login_history.login)),NULL), login_history.userid
from login_history, outer members
where login_history.userid = members.userid
group by login_history.userid

edited Jan 06 '11 at 19:10

answered Jan 06 '11 at 18:47

CheeseConQueso

5,831
29
93
126

`nvl` → `COALESCE`, no reason not to use the standard function. Actually, with a second arg of null, can't you just omit it entirely? – derobert Jan 06 '11 at 18:50
@derobert - i thought they were both standard? – CheeseConQueso Jan 06 '11 at 18:53
1

@CheeseConQueso: nvl is an Oracle extension. See also http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce – derobert Jan 06 '11 at 18:54
@derobert - i think it will skip them and you would have to outer join in that case of eliminating `nvl()` or `coalesce()` – CheeseConQueso Jan 06 '11 at 18:55
@CheeseConQueso: Actually, I think you need the outer join anyway, if you want to guarantee getting a row back for each members.userid. So that should probably be `from members m left join login_history lh on (m.userid = lh.userid)` and the select should be `m.userid`. – derobert Jan 06 '11 at 19:09
@derobert - yeah, i was thinking the same. except `from login_history, outer members` as the only change – CheeseConQueso Jan 06 '11 at 19:10
@CheeseConQueso: I think that `, outer members` syntax might be an Oracle-ism too... And you definitely need to change the select clause as well (because it will be null). Also, I missed it, you need to change the group by to be `m.userid` too. – derobert Jan 06 '11 at 19:13
@derobert - it is oraclism... haha - leave an answer, vote me down, and curse me off! just kidding, but im not just going to rip off your ideas into my answers past the `outer` which i had considered on my own anyway – CheeseConQueso Jan 06 '11 at 19:18
@CheeseConQueso: No, please go ahead and update your answer. If it feels wrong to be getting credit for my suggestions, just put a thanks at the bottom. – derobert Jan 06 '11 at 19:25
You need to draw the userid column from the _members_ table, not from login_history, for this to work. – Larry Lustig Jan 06 '11 at 19:57
@larry - yeah thats what derobert was saying too – CheeseConQueso Jan 06 '11 at 19:58
Thanks. I'm sure this works. I'm using SQL Server. Don't know what NVL function do or what outer is. – Yada Jan 07 '11 at 01:04
@yada - `nvl()` and similar functions explained here - http://www.w3schools.com/sql/sql_isnull.asp. `outer` types are explained here - http://en.wikipedia.org/wiki/Join_%28SQL%29. in a nutshell, `nvl()` handles the secondary selection criteria found if first field is of `NULL` value, and `outer` handles how to join tables. `select field from table, outer another_table` will select values from `another_table` even if corresponding key is not in `table` – CheeseConQueso Jan 07 '11 at 14:09

score 1 · Accepted Answer · answered Jan 06 '11 at 19:38

1

 SELECT m.userid, MAX(l.login) 
   FROM members m LEFT OUTER JOIN login_history l
   ON m.userid = l.userid GROUP BY m.userid

answered Jan 06 '11 at 19:38

Larry Lustig

49,320
14
110
160

@Yada, as far as I can tell, it's the only one posted so far that will work. – Larry Lustig Jan 06 '11 at 20:12
Thanks. Need to keep this snippet in the memory bank. – Yada Jan 07 '11 at 01:03

Last login joining two tables

2 Answers2