Group By Clause In Same Table - 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 Clause In Same Table

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-02-22 : 12:35:30

I am having some problems with a group by clause...

I have a table called computer_info
computer_name
computer_lastlogon
computer_dc

Sample data
MyComput1, 02/15/2012, DC1
MyComput1, 02/11/2012, DC2
MyComput1, 02/14/2011, DC3
MyComput1, 01/12/2012, DC4
MyComput2, 01/15/2012, DC1
MyComput2, 01/11/2012, DC2
MyComput2, 01/14/2011, DC3
MyComput2, 01/23/2012, DC4

I was hoping for the out put of each computer_name with the latest computer_lastlogon time.

Desired Result
MyComput1, 02/15/2012, DC1
MyComput2, 01/23/2012, DC4

Here is the query i have now, but it dosent seem to return the rights computer_lastlogon Date.
select computer_samaccountname, max(computer_lastlogon) from computer_info group by computer_samaccountname

Thanks guys!

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-22 : 12:38:28

SELECT * FROM computer_info o WHERE EXISTS (
SELECT * FROM computer_info i
WHERE o.computer_name = i.computer_name
GROUP BY i.computer_name
HAVING o.computer_lastlogon = MAX(i.computer_lastlogon))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-02-22 : 12:39:48

Wow that was fast x002548 thanks! I love this place :)
I guess it helps to put very specific info :)

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-02-22 : 13:30:42

I ran your query, and it seems to not bring back the correct result set.

When i was doing some spot checking, i decided to pull back all the records for 1 of the accounts in the computer_info table to see, and as you see the date returned should have been 02/22/2012. But in the results query you gave, it returns 12/23/2009

Result Row:

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-22 : 13:40:21

Sorry, the code is correct..don't know what to tell ya



CREATE TABLE #computer_info (
computer_name varchar(50)
, computer_lastlogon datetime
, computer_dc varchar(10)
)
GO

INSERT INTO #computer_info (computer_name, computer_lastlogon, computer_dc)
SELECT 'MyComput1', '02/15/2012', 'DC1' UNION ALL
SELECT 'MyComput1', '02/11/2012', 'DC2' UNION ALL
SELECT 'MyComput1', '02/14/2011', 'DC3' UNION ALL
SELECT 'MyComput1', '01/12/2012', 'DC4' UNION ALL
SELECT 'MyComput2', '01/15/2012', 'DC1' UNION ALL
SELECT 'MyComput2', '01/11/2012', 'DC2' UNION ALL
SELECT 'MyComput2', '01/14/2011', 'DC3' UNION ALL
SELECT 'MyComput2', '01/23/2012', 'DC4'
GO



SELECT * FROM #computer_info o 
 WHERE EXISTS (
				SELECT * FROM #computer_info i 
				 WHERE o.computer_name = i.computer_name
			  GROUP BY i.computer_name 
				HAVING o.computer_lastlogon = MAX(i.computer_lastlogon))
				GO
				
DROP TABLE #computer_info
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-02-22 : 13:43:52

Would it matter if the computer_lastlogon field was a varchar, and not a datetime?

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-22 : 13:47:55

Doesn't appear so....you can cut and paste my code and change it to varchar to see..though it might pick a different value....but it still would be only 1..unless there are 2 with the same value

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-22 : 19:00:50

did you cut and paste the sample code and un it in SSMS?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources