Oracle Statement to Return Result Depending on Max Value of a Record

0
votes

I've been stuck here for the whole day.

I have 2 tables:

TABLE 1: LOGS
FIRST_NAME      LAST_LOG_IN
------------------------------
John            15-NOV-17
Jane            13-NOV-17
John            12-NOV-17
John            11-NOV-17
John            10-NOV-17
Jane            12-NOV-17

TABLE 2: USER
FIRST_NAME       LAST_NAME    
------------------------------
Jane             Doe
John             Smith

The Requirements are:

  • If today is 22-NOV-17, I need to get the FIRST_NAME, LAST_NAME and LAST_LOG_IN(in 'YYYY-MM-DD HH24:MI:SS' format) of the user only if his/her LAST_LOG_IN is earlier than the last 7 days

  • Put only in one statement to execute in SQL Developer (not using PL/SQL)

so the Expected Output should be:

FIRST_NAME       LAST_NAME        LAST_LOG_IN
---------------------------------------------------
Jane             Doe            2017-11-13 17:49:57
1
sqloracleoracle9i

1 Answers

0
votes

Something like:

select u.first_name, u.last_name, to_char(a.log_date,'YYYY-MM-DD HH24:MI:SS') from (
  select first_name, max(last_log_in) log_date from logs group by first_name) a
  inner join 
  user u on (a.first_name = u.first_name)
where a.log_date > sysdate - 7;