Select Max value of Joined tables

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

SQL Server 2005 Forums

Transact-SQL (2005)

Select Max value of Joined tables

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-03-02 : 15:42:21

I have the below table that is produced by join 4 tables. I was hoping someone would be able to help me produce TSQL that will result in only the highest Count for each UUID. So 1234 would be Joe and 9998 would be Tom.

UUID HostName SerialNumber User Count
1234 Host1234 abcd12345 Joe 10
1234 Host1234 abcd12345 Mark 2
1234 Host1234 abcd12345 Sara 1
9998 Host998 zzxxyy99 Tom 30
9988 Host998 zzxxyy99 Jerry 1

Thanks!

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-03-02 : 15:46:51

The code I have right now to produce the above table is:

Select dis_hw.dis_hw_uuid, dis_hw.host_name,
dis_hw.serial_number, userr.user_name, link_user.ref_count
from link_user join dis_hw on link_user.dis_hw_uuid = dis_hw.dis_hw_uuid
join userr on link_user.user_uuid = userr.user_uuid
join agent on link_user.dis_hw_uuid = agent.object_uuid
order by serial_number, ref_count desc

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-03-02 : 16:23:43

Select dis_hw.dis_hw_uuid, dis_hw.host_name,
dis_hw.serial_number, userr.user_name, link_user.ref_count
from link_user join dis_hw on link_user.dis_hw_uuid = dis_hw.dis_hw_uuid
join userr on link_user.user_uuid = userr.user_uuid
join agent on link_user.dis_hw_uuid = agent.object_uuid
order by serial_number, ref_count desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 16:53:19

[code]
;With UserCountTemp
AS
(
Select dis_hw.dis_hw_uuid, dis_hw.host_name,
dis_hw.serial_number, userr.user_name, link_user.ref_count
from link_user join dis_hw on link_user.dis_hw_uuid = dis_hw.dis_hw_uuid
join userr on link_user.user_uuid = userr.user_uuid
join agent on link_user.dis_hw_uuid = agent.object_uuid
)

SELECT u.*
FROM UserCountTemp u
INNER JOIN (SELECT UUID, HostName, SerialNumber,MAX(ref_count) AS ref_count
FROM UserCountTemp
GROUP BY UUID, HostName, SerialNumber
)u1
ON u1.UUID = u.UUID
AND u1.HostName = u.HostName
AND u1.SerialNumber = u.SerialNumber
AND u1.ref_count = u.ref_count
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-03-05 : 12:56:36

Fantastic!!!

Thanks very much for your help, that is a great solution and will be put to use!

Thanks again

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-05 : 13:45:07

or cold the old fashion way

code that's cross platform


	SELECT lo.dis_hw_uuid
		 , do.host_name
		 , do.serial_number
		 , uo.user_name
		 , lo.ref_count
	  FROM link_user lo 
 LEFT JOIN dis_hw	 do ON lo.dis_hw_uuid = do.dis_hw_uuid
 LEFT JOIN userr	 uo ON lo.user_uuid   = uo.user_uuid 
 LEFT JOIN agent	 ao ON lo.dis_hw_uuid = ao.object_uuid
	 WHERE EXISTS (SELECT * FROM link_user li LEFT JOIN dis_hw di 
					   ON li.dis_hw_uuid = di.dis_hw_uuid
					WHERE li.dis_hw_uuid = lo.dis_hw_uuid   
				 GROUP BY li.dis_hw_uuid
				   HAVING lo.ref_count = MAX(li.ref_count))
order by do.serial_number, lo.ref_count desc

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