Last n of users and time they last logged in 2

[jimoo]

I have 2 tables.

Table 1 has users: users
userid
firstname
lastname
ect...

Table 1 has login history: loginHistory
userid
loggedindate (datetime field)

Some users login often. I want uniques (lets say last 100) usernames and the last date/time they logged in.



Jim

 

[RiverGuy]

SELECT a.*
FROM [users] a

INNER JOIN

(SELECT TOP 100 UserID, MAX(LoggedInDate) AS LastLogin
FROM loginHistory
GROUP BY UserID
ORDER BY MAX(LoggedInDate) DESC) b

ON a.UserID = b.UserID

 

[markros]

RiverGuy,

The above code is not going to work. You can not combine TOP and GROUP BY

Try

;with cte as (select U.*, L.LoginDate row_number() over (partition by U.UserID
order by L.LoginDate DESC) as Row from Users U inner join LoginHistory L on U.UserID = L.UserID)

select * from cte where Row <=100

PluralSight Learning Library

 

[RiverGuy]

???

Does this not work for you?

SELECT TOP 3 TABLE_NAME, COUNT(*) AS CT FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
ORDER BY COUNT(*) DESC

 

[markros]

No, it will not return top 3 per group. I think that was the requirement. It will just return 3 top records.

That's also what I meant, sorry for wrong wording.

PluralSight Learning Library

 

[markros]

But after I re-read the original message, I think your code was what we wanted and I just read it too quickly the first time. He wants 100 unique names with last date not 100 last logins per user as I interpreted.

PluralSight Learning Library

 

[RiverGuy]

Yes, I took it to mean that he basically wanted "The last 100 users to log in in addition to the latest time each logged in.

 

[jimoo]

RiverGuy

I tried your first example many ways and could not get it to work. Note the table names are actually userProfile and LoginAudit and are joined by LoginAudit.UserName = UserProfile.LoginUserName



-- 1 way no alias: Syntax Error
SELECT UserProfile.* from UserProfile
INNER JOIN (SELECT TOP 100 UserName, MAX(LoggedInDate) AS LastLogin
FROM LoginAudit
GROUP BY UserName
ORDER BY MAX(LoggedInDate) DESC) ON LoginAudit.UserName = UserProfile.LoginUserName

-- using the alias: Syntax Error
SELECT UserProfile.* from UserProfile up
INNER JOIN (SELECT TOP 100 la.UserName, MAX(LoggedInDate) AS LastLogin
FROM LoginAudit la
GROUP BY la.UserName
ORDER BY MAX(LoggedInDate) DESC) ON la.UserName = up.LoginUserName

-- this works but is not what I am looking for
SELECT TOP 3 TABLE_NAME, COUNT(*) AS CT FROM INFORMATION_SCHEMA.COLUMNS GROUP BY TABLE_NAME ORDER BY COUNT(*) DESC


MarkRos - this did not work: Syntax Error. Tried it several ways
with cte as (select U.*, L.LoginDate row_number()
over (partition by U.UserIDorder by L.LoginDate DESC) as Row
from UserProfile U inner join LoginAudit L on U.LoginUserName = L.Username)
select * from cte where Row <=100

Jim

 

[gmmastros]

Try this:

SELECT Top 100 UserProfile.*, LastLoginAudit.LastLogin
from   UserProfile 
       INNER JOIN 
         (
         SELECT   UserName, 
                  MAX(LoggedInDate) AS LastLogin
         FROM     LoginAudit 
         GROUP BY UserName
         ) As LastLoginAudit
         ON LastLoginAudit.UserName = UserProfile.LoginUserName
Order By LoginAudit.LastLogin DESC

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jimoo]

That worked with one minor correcton

I changed the following statement from:

Order By LoginAudit.LastLogin DESC


To:
Order By LastLoginAudit.LastLogin DESC



Jim

 

[markros]

In RiverGuy's way you were missing an alias for derived table and in my way (although it was not what you requested) you were missing a command before row_number(), e.g.

with cte as (select U.*, L.LoginDate, row_number()
over (partition by U.UserIDorder by L.LoginDate DESC) as Row
from UserProfile U inner join LoginAudit L on U.LoginUserName = L.Username)
select * from cte where Row <=100

PluralSight Learning Library