inner join order - 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)

inner join order

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-07-08 : 08:40:46

i have

select * from users
LEFT OUTER JOIN
D ON dbo.users.Registration = dbo.D.Registration

now this works but d.registration can have more then one record with the same registration -

how can i make the query always pull the last registration entered (highest id) where d.temporary=0

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-08 : 08:42:27

create a subquery with your condition and join to that
select ...
from t1
join (select ... where ... ) t1 on t1.id = t2.id

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-08 : 11:30:43

No need if you use the new ROW_NUMBER() function in SQL Server 2005

SELECT	<col list here>
FROM	(
		SELECT		u.<col list here>,
				ROW_NUMBER() OVER (PARTITION BY u.UserID ORDER BY d.Registration DESC) AS RecID
		FROM		dbo.Users AS u
		LEFT JOIN	dbo.D AS d ON d.Registration = u.Registration AND d.Temporary = 0
	) AS d
WHERE	RecID = 1

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources