CODEWITHSUNDEEP
sql
AndyBSG
AndyBSG

Reputation: 37

SQL To Find Most RECENT row Using GROUP BY

SQL newbie hear tearing my hair out trying to work this one out! I have a problem that is similar to this.

I have the following data and all fields are defined as CHARACTER - No DATE or TIME unfortunately thanks to poor design by the original DBA

Surname    Name     LoginDate   LoginTime

Smith      John     2014-06-25  13.00
Smith      John     2014-06-24  14.00 
Smith      Susan    2014-06-26  09.00
Smith      Susan    2014-06-26  11.30
Jones      Bill     2014-06-25  09.30
Jones      Bill     2014-06-25  12.30
Jones      Bill     2014-06-26  07.00

What i'm trying to get on my output is the most recent log in for each person so I would expect to see

Smith      John     2014-06-25  13.00
Smith      Susan    2014-06-26  11.30
Jones      Bill     2014-06-26  07.00

I've tried different combinations of temporary tables, using CONCAT on the Date and Time and the MAX function but I just keep drawing a blank. I think I know the tools and commands I need to use I just can't seem to string them all together properly.

I know I have to group them by name/surname then somehow combine the date and time in a way that lets me use the MAX function but when I output them I can never seem to get the LoginDate and LoginTime to appear as seperate fields on the output because they're not included in any GROUP BY that I use.

Is anyone able to show me how to do this as I haven't got a lot of hair to start with :)

Upvotes: 0

Views: 83

Answers (5)

TMNT2014
TMNT2014

Reputation: 2130

Try this Query - 

With MaxTimeStamp as 
(
 SELECT Surname, Name, Max(TIMESTAMP(LoginDate, LoginTime)) as LoginDateTime
 FROM   YourTable 
 group by Surname, Name
)
select c.Surname, c.Name, d.LoginDate, d.LoginTime,
from MaxTimeStamp c 
Join  YourTable d 
      on  c.Surname = d.Surname
      and  c.Name       = d.Name
      and  Date(c.LoginDateTime)    = d.LoginDate
      and  Time(c.LoginDateTime)    = d.LoginTime

Upvotes: 2

dunean
dunean

Reputation: 166

I have split it into two parts, to find the newest login per user and do the group by. I then join to the data again to find the record.

select 
    dt.Surname
    , dt.Name
    , dt.LoginDate
    , dt.LoginTime 
from 
    dataTable dt 
    join
        (select Surname, Name, MAX(LoginDate+LoginTime) ts from #temp group by surname, name) sub 
        on sub.Name = dt.Name
        and sub.Surname = dt.Surname
    and dt.LoginDate+dt.LoginTime = ts
WHERE
    not sub.Name is null

You can also do it without the need to join, by splitting the timestamp out again.

select 
    sub.Surname
    , sub.Name
    , LEFT(ts,10) LoginDate
    , RIGHT(ts,5) LoginTime 
from 
    (select Surname, Name, MAX(LoginDate+LoginTime) ts from #temp group by surname, name) sub

Upvotes: 0

user3793337
user3793337

Reputation: 36

Why not a group by? i´m missing anything?

Select name, surname, LoginDate, max(LoginTime) from table where (name,surname,login_date) in (select name, surname, max(LoginDate) from table group by name,surname) group by name, surname, LoginDate

If the LoginDate is not a date or similar datatype, it should run i guess...

Br.

Upvotes: 0

Siyual
Siyual

Reputation: 16917

In addition to Gordon Linoff's answer, if your SQL database supports window functions, you can also use this to get the desired result:

;With Cte As
(
    Select  *, 
            Row_Number() Over (Partition By Surname, Name Order By LoginDate Desc, LoginTime Desc) RN
    From    Table
)
Select  Surname, Name, LoginDate, LoginTime
From    Cte
Where   RN = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271121

You don't do this using group by. Here is a method using not exists:

select t.*
from table t
where not exists (select 1
                  from table t2
                  where t2.name = t.name and t2.surname = t.surname and
                        (t2.logindate > t.logindate or
                         t2.logindate = t.logindate and
                         t2.logintime > t.logintime
                 );

This transforms the query to: "Get me all rows from the table where there is no row with the same name and a later login." It would be simpler if the login information were stored as a single datetime.

Also, the above will work in just about all SQL databases. Many databases support window functions which can also be used for this problem.

Upvotes: 0

Related Questions