1

Topic: How to select from a broad gull of a line with the maximum (last) dates?

The beginner in SQL, I ask not to kick strongly smile
There is a task to select time of the last login from the broad gull table. The type table:
LAST_UPDATE, EVENT_NAME, USER_LOGIN
11/10/2017, login, ivanov
12/15/2017, login, ivanov
12/20/2017, logout, ivanov
1/10/2018, login, ivanov
11/10/2017, login, petrov
12/15/2017, login, petrov
12/20/2017, logout, petrov
1/10/2018, login, petrov
It is necessary to receive as a result type sampling:
petrov 1/10/2018
ivanov 1/10/2018
Such variant is true for the given task:
select USER_LOGIN, max (LAST_UPDATE) from LOG where EVENT_NAME ='Login ' group by USER_LOGIN

2

Re: How to select from a broad gull of a line with the maximum (last) dates?

Firef1ash, you were praised or what?

3

Re: How to select from a broad gull of a line with the maximum (last) dates?

Firef1ash wrote:

There is a task to select time of the last login from the broad gull table. The type table:

"To select time of the last login" and "to select time of the last login for each user"
different tasks
Time of the last login  is formal 1/10/2018
.....
stax

4

Re: How to select from a broad gull of a line with the maximum (last) dates?

Firef1ash;
Give I will guess. It is necessary to you so?

with table1 (last_update, event_name, user_login) as
(
select to_date (' 11/10/2017 ', ' dd.mm.yyyy '), ' login ', ' ivanov ' from dual union all
select to_date (' 12/15/2017 ', ' dd.mm.yyyy '), ' login ', ' ivanov ' from dual union all
select to_date (' 12/20/2030 ', ' dd.mm.yyyy '), ' logout ', ' ivanov ' from dual union all
select to_date (' 1/10/2018 ', ' dd.mm.yyyy '), ' login ', ' ivanov ' from dual union all
select to_date (' 11/10/2017 ', ' dd.mm.yyyy '), ' login ', ' petrov ' from dual union all
select to_date (' 12/15/2017 ', ' dd.mm.yyyy '), ' login ', ' petrov ' from dual union all
select to_date (' 2/20/2040 ', ' dd.mm.yyyy '), ' logout ', ' petrov ' from dual union all
select to_date (' 1/30/2018 ', ' dd.mm.yyyy '), ' login ', ' petrov ' from dual
)
select
t. *;
last_value (last_update) over (partition by user_login order by decode (event_name, ' login ', 1, 0), last_update rows between unbounded preceding and unbounded following) last_user_login_dt
from table1 t
order by user_login, last_update;

5

Re: How to select from a broad gull of a line with the maximum (last) dates?

merch;
Sorry, forgot a sign on a question after a phrase "such variant it is true for the given task:" smile

6

Re: How to select from a broad gull of a line with the maximum (last) dates?

Stax;
Yes, "to select time of the last login for each user"

7

Re: How to select from a broad gull of a line with the maximum (last) dates?

Firef1ash;

SELECT A.USER_LOGIN, MAX (A.LAST_UPDATE) LASTDATE
FROM LOGTABLE A
GROUP BY A.USER_LOGIN

8

Re: How to select from a broad gull of a line with the maximum (last) dates?

Firef1ash;
It is true
ps
logout for  is, login is not present, it is necessary to deduce?
.....
stax

9

Re: How to select from a broad gull of a line with the maximum (last) dates?

xenix wrote:

Firef1ash;

SELECT A.USER_LOGIN, MAX (A.LAST_UPDATE) LASTDATE
FROM LOGTABLE A
GROUP BY A.USER_LOGIN
wrote:

time last login

....
stax

10

Re: How to select from a broad gull of a line with the maximum (last) dates?

At grouping we view all records, then we define "last" and we deduce.
Whether and we can begin review with "end" (on rownum, for example) [not order which at us starts to sort after sampling] and to return the first line answering to a condition?

11

Re: How to select from a broad gull of a line with the maximum (last) dates?

Dshedoo, and you the troll.