Group By Basics - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By Basics

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-19 : 12:53:25

If i have a table that has multiple entries, like a log, of someone logging into a system, that tracks like below.

table
userid
computername
appid
logintime

data
1,MEME,2, 10-15-2012
1,MEME,2, 10-16-2012
1,MEME,3, 10-17-2012
1,MEME2,4, 10-18-2012
2,MEME2,2, 10-23-2012
2,MEME,2, 10-22-2012
1,MEME2,3, 10-21-2012
2,MEME2,4, 10-20-2012

RESULT SHOULD BE
1,MEME2,4, 10-18-2012
1,MEME2,3, 10-21-2012
2,MEME2,2, 10-23-2012
2,MEME2,4, 10-20-2012

How can i get it so even though it shows ID 1 has logged into the same machine multiple times in the log, i just want to show each users, and the last time they logged into app, even though they might have multiple entries for the same computer and app, just different times...

I dont know how i group all logs for a userid and a appid, and then out of each of those records, give me the top date for that user, for that id..

thanks guys for the help.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 13:07:00

Hello jjmusicpro,

Do you mean something like the following?


SELECT t.* 
FROM @table t
JOIN 
(
	SELECT
		userid,
		computername,
		MAX(loginTime) as lastLogin
	FROM @table
	GROUP BY 	
		userid,
		computername
) d ON d.userID = t.userID AND d.computername = t.computername AND d.lastLogin = t.loginTime
ORDER BY userid, computername, logintime

HTH.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-19 : 13:08:50

what does the @ sign mean on the table name?

The actual collumn names are

LogUserID
LogComputerID
LogApplicationID
LogTime

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 13:12:00

quote:
Originally posted by jjmusicpro

what does the @ sign mean on the table name?

It represents a table variable (a local in memory container). You can read more about it here:

http://www.sqlteam.com/article/using-table-variables

But if you wish to test the query, simply replace the @table and field names with your tablename and fields.

HTH.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-19 : 13:18:07

I dont think that worked, since it still shows multiple records for each instance of a persons apps, it should only 1 time and that is the latest time of the LogTime for each persons app

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 13:26:17

quote:
Originally posted by jjmusicpro

Ok i think i got it, where would i put my join data, to grab other values from other tables?

Good to hear - Glad that helped you,

I recommend you add the additional joins on the outside.

I find it is best to build up a query from the inside out. Where the inside has the base set of data and the further out you move, the more fields you add to the constrained set.

So the inside query gives us our base set of constrained data and then we move out to get the additional data.

HTH.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-19 : 13:30:22

Sorry thought it looked right, but looks like LogApplicationID still show multiple rows in the result for the same person..

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 13:31:51

quote:
Originally posted by jjmusicpro

I dont think that worked, since it still shows multiple records for each instance of a persons apps, it should only 1 time and that is the latest time of the LogTime for each persons app

When I examine the sample data you provided against the query I provided, it appears to be giving the correct results:

declare @table TABLE (userid int, computername varchar(10), appid int, logintime datetime)

insert @table 
select 1,'MEME',2, '10-15-2012' union all
select 1,'MEME',2, '10-16-2012' union all
select 1,'MEME',3, '10-17-2012' union all
select 1,'MEME2',4, '10-18-2012' union all
select 1,'MEME2',3, '10-21-2012' union all
select 2,'MEME2',2, '10-23-2012' union all
select 2,'MEME',2, '10-22-2012' union all
select 2,'MEME2',4, '10-20-2012'
/*

RESULT SHOULD BE
1,MEME2,4, 10-18-2012
1,MEME2,3, 10-21-2012
2,MEME2,2, 10-23-2012
2,MEME2,4, 10-20-2012
*/

--This desired result seems incorrect/incomplete, based on the sample data provided.

SELECT t.* 
FROM @table t
JOIN 
(
	SELECT
		userid,
		computername,
		MAX(loginTime) as lastLogin
	FROM @table
	GROUP BY 	
		userid,
		computername
) d ON d.userID = t.userID AND d.computername = t.computername AND d.lastLogin = t.loginTime
ORDER BY userid, computername, logintime

If not, then I am not sure I understand your problem correctly.

Wish I could be of more help.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 13:35:40

quote:
Originally posted by jjmusicpro

Sorry thought it looked right, but looks like LogApplicationID still show multiple rows in the result for the same person..

This looks to be because they are different computerid's.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-19 : 13:56:03

The computerID i really dont care about, its the LogApplicationID that need to be grouped per user

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 13:57:53

quote:
Originally posted by jjmusicpro

The computer ID's dont matter.

Sorry I cannot help you. I do not understand what you are trying to accomplish. Perhaps another does and will provide some helpful input.

Best wishes.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-19 : 14:13:32

quote:
Originally posted by ehorn

quote:
Originally posted by jjmusicpro

The computer ID's dont matter.

Wouldnt i just updated the GROUP BY?

Sorry I cannot help you. I do not understand what you are trying to accomplish. Perhaps another does and will provide some helpful input.

Best wishes.

The result need to show like below.
Since there can be multiple LogTime Events, i just need to look at each LogUserID, and group all the LogApplicationsID's for that user, and return the latest LogTime For each.

So the result should show the high lighted items:
The ones that are not high lighted, have the same LogApplicationID, but with a ealier date, so i dont want to show those..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-19 : 14:19:28

I don't think I am going to be of much help either, because I am confused by your sample output. For userid = 1 there are three appids in the sample input data, appids 2,3, 4. The sample output shows two appids, 3 and 4 four this userid. Yet, you said "its the LogApplicationID that need to be grouped per user". What was the logic used to eliminate appid 2 from the results?

quote:
table
userid
computername
appid
logintime

data
1,MEME,2, 10-15-2012
1,MEME,2, 10-16-2012
1,MEME,3, 10-17-2012
1,MEME2,4, 10-18-2012
2,MEME2,2, 10-23-2012
2,MEME,2, 10-22-2012
1,MEME2,3, 10-21-2012
2,MEME2,4, 10-20-2012

RESULT SHOULD BE
1,MEME2,4, 10-18-2012
1,MEME2,3, 10-21-2012
2,MEME2,2, 10-23-2012
2,MEME2,4, 10-20-2012

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 14:20:40


SELECT LogUserID, LogApplicationID, MAX(LogTime) As LastLogTime
FROM <youtTable>
GROUP BY LogUserID, LogApplicationID,

If this gives you the records you need, then simply join it back to the table (and others) to get the additional fields you need. Similar to the initial query just a different grouping.

IMHO, It would help you to understand how GROUP BY works so that you can build these going forward. In my initial example, I used GROUP BY within a derived table and then joined back to the table to get the additional fields.

So the SQL concepts are 'GROUP BY' and its application and 'Derived tables' and their uses.

HTH. Best wishes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources