count the last login for each user per day

Question

How to count last login for each user per day.?

Table looks similar to this

id  name   Date 
-----------------
1    a    01-Feb-2017
2    a    01-Feb-2017
3    a    01-Feb-2017
4    b    02-Feb-2017
5    b    02-Feb-2017
6    c    04-Feb-2017
7    c    04-Feb-2017

All i want to do is put to_char(date,'dd-mm-yy-hh-mm-ss) to the date as my date do not have times in it.
Fetch the latest login for user a on 1-feb-2017 , user b on 2-feb-2017 and user c on 4-feb-2017
One user can login multiple times, so the dates are printed many times, but i want to show only last login date.

Kindly let me know please.....looks simple but i'm stuck

In some (most) case, this could be done with `GROUP BY` and using `MAX(date)` (depends on the DBMS) — AxelH, Feb 14 '17 at 13:08
Which dbms are you using? (Many products are far from ANSI SQL compliant when it comes to date/time.) — jarlh, Feb 14 '17 at 13:09

MT0 · Accepted Answer · 2017-09-15T12:52:03.243

How to count last login for each user per day.?

Group the results by user (i.e. name) and each day (i.e. TRUNC( "Date" )) and then to get the last value per group you can take the maximum:

SELECT name, MAX( "Date" )
FROM   your_table
GROUP BY name, TRUNC( "Date" )

my date do not have times in it.

Yes, they do. SQL Developer is just not showing it.

Either, change the NLS_DATE_FORMAT parameter - as demonstrated in this answer.

Or alter the oracle session parameter.

Or format the output as a string:

SELECT name,
       TO_CHAR( MAX( "Date" ), 'YYYY-MM-DD HH24:MI:SS' ) AS last_date
FROM   your_table
GROUP BY name, TRUNC( "Date" )

Works great. Thanks alot :) – Codework Feb 15 '17 at 09:45 — Codework, Feb 15 '17 at 09:45

devoh · Answer 2 · 2017-02-15T07:33:34.337

0

Try this :

SELECT name, MAX(TO_DATE("Date", 'DD-MM-YYYY')) last_date
FROM 
(
    SELECT 1 AS id, 'a' AS name, '01-Feb-2017' AS "Date" 
        FROM dual UNION
    SELECT 2, 'a', '01-Feb-2017'
        FROM dual UNION
    SELECT 3, 'a', '01-Feb-2017'
        FROM dual UNION
    SELECT 4, 'b', '02-Feb-2017'
        FROM dual union
    SELECT 5, 'b', '02-Feb-2017'
        FROM dual UNION
    SELECT 6, 'c', '04-Feb-2017'
        FROM dual UNION
    SELECT 7, 'c', '04-Feb-2017'
        FROM dual
) mytable
GROUP BY name

Output:

    NAME    LAST_DATE
1   a   01.02.2017 00:00:00
2   c   04.02.2017 00:00:00
3   b   02.02.2017 00:00:00

Explanation :

You group your result by name, you want the last date, thus max(date). But you have a string as date. So first, you convert it into a datetime.

If you want a different output could you provide it to us ... cause it's unclear at the moment.

edited Feb 15 '17 at 07:33

answered Feb 14 '17 at 13:27

devoh

867
9
13

From the [Oracle documentation](https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions027.htm#SQLRF00620): `CONVERT` converts a character string from one character set to another. This does not appear to be an Oracle answer. You probably mean to use the `TRUNC` function. – MT0 Feb 14 '17 at 14:06
my bad. in the first post edit, the tag was sql-server or at least It thought it was – devoh Feb 14 '17 at 14:09
The [sql] tag means the SQL language and is not a short hand for MS SQL Server (although some posters do use it as such). – APC Feb 14 '17 at 15:05

count the last login for each user per day

2 Answers2