Latest Date with Join - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Latest Date with Join

Author

Topic

jaggster
Starting Member

3 Posts

Posted - 2008-02-13 : 11:24:53

I am having difficulty getting a Select statement to return the results I need and request a little help. I will include the Select statement I am working with and description what I need to add to it.

Current Select:

SELECT Device.AssetNumber, Device.UserName, User.Last_Name, User.Update
FROM Device
LEFT OUTER JOIN User ON Device.AssetNumber = User.AssetNumber
WHERE User.Last_Name NOT LIKE Device.UserName

Device.AssetNumber is a number
Device.UserName is text - "last name, first name"
User.Last_Name is text
User.Update is date of update

Since the AssetNumber can be assigned to multiple Users, this query currently returns a list of Asset Numbers where the Last_Name in the User table does not match the beginning of the UserName in the Device table.

This is working fine as is. However, I need to make a slight change so that for multiple users of an AssetNumber the query only returns the user who has the latest Update field in the User table.

Example:

Current results are:

AssetNumber UserName Last_Name Update
123 Smith, John Martin 02/01/2008
345 Roberts, Jim Smith 02/04/2008
345 Roberts, Jim Sanders 02/06/2008

Results needed:

AssetNumber UserName Last_Name Update
123 Smith, John Martin 02/01/2008
345 Roberts, Jim Sanders 02/06/2008

Any help would be appreciated. Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 11:46:03

Play around with ROW_NUMBER() function.
There are many examples here on SQLTeam.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 11:49:57

[code]SELECT Device.AssetNumber, Device.UserName, User.Last_Name, User.Update
FROM Device
LEFT OUTER JOIN
(SELECT ROW_NUMBER() OVER (PARTITION BY AssetNumber ORDER BY Update DESC) AS RowNo,
AssetNumber,
Last_Name,
Update
FROM User )u
ON Device.AssetNumber = u.AssetNumber
AND u.RowNo=1
WHERE User.Last_Name NOT LIKE Device.UserName

[/code]

jaggster
Starting Member

3 Posts

Posted - 2008-02-13 : 12:26:17

Thank you both for your suggestion. I did try to apply the ROW_NUMBER() functionality to my Select statement but kept getting a message in the query builder that OVER SQL construct is not supported. I checked with our server group and was told the server these databases are on is SQL 2000, not 2005. My appologies for posting this question in this forum. I was under the impression all our servers were SQL 2005 now. Again, sorry. I guess this is not going to be easy to resolve.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 12:31:52

This will work as long as there are only one update per asset per day

DECLARE	@Sample TABLE (AssetNumber INT, UserName VARCHAR(200), Last_Name VARCHAR(200), [Update] DATETIME)

INSERT	@Sample
SELECT	123, 'Smith', 'John Martin', '02/01/2008' UNION ALL
SELECT	345, 'Roberts', 'Jim Smith', '02/04/2008' UNION ALL
SELECT	345, 'Roberts', 'Jim Sanders', '02/06/2008'

SELECT		t1.*
FROM		@Sample AS t1
INNER JOIN	(
			SELECT		AssetNumber,
					U
					MAX([Update]) AS [Update]
			FROM		@Sample
			GROUP BY	AssetNumber
		) AS t2 ON t2.AssetNumber = t1.AssetNumber
WHERE		t1.[Update] = t2.[Update]

E 12°55'05.25"
N 56°04'39.16"

jaggster
Starting Member

3 Posts

Posted - 2008-02-13 : 14:08:04

Hey Peso, thanks! But I see one problem with this solution. In the WHERE clause you are comparing the Update field from t2 and t1, yet in my situation it only exists in t2...there is not date in t1.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-14 : 01:54:22

t2 and t1 are aliases for same table.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources