Inner join with top x rows. - 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 2008 Forums

Transact-SQL (2008)

Inner join with top x rows.

Author

Topic

Drew Westling
Starting Member

8 Posts

Posted - 2012-04-13 : 09:39:21

I've been set infront of a problem where I want to load the X last items related to a set of users. I can't wrap my head around on how to do this query and hope that any of you guys can give me a helpful hand.

Simplified structure is as follows:

Create table [User] (
userId Int not null,
email nvarchar(255));

Create table [UserModification] (
userID int not null,
modifyingUserID int not null,
time datetime not null);

Now i want to load the say 10 last UserModifications to all users that have email like '%microsoft.com'.

Writing dynamiq sql with joins is so far the only solution I've got, but I refuse to make such a butt ugly hack. :)

Hope any of you've got a solution for this.

Kind regards
Drew

Things should be as simple as possible, not simpler.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-13 : 09:59:23

SELECT u.*, f.ModyfyingUserID, f.[Time]
FROM dbo.[User] AS u
CROSS APPLY (SELECT TOP(10) um.ModyfyingUserID, um.[Time] FROM dbo.UserModification AS um WHERE um.[UserID] = u.[UserID] ORDER BY um.[Time] DESC
) AS f
WHERE u.email LIKE '%@microsoft.com'

N 56°04'39.26"
E 12°55'05.63"

Drew Westling
Starting Member

8 Posts

Posted - 2012-04-13 : 10:08:02

Wonderful, thank you! Works like a charm.

I have actually never seen this syntax before, it sort of makes me feel like a newbie. ;)

Things should be as simple as possible, not simpler.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:57:00

quote:
Originally posted by Drew Westling

Wonderful, thank you! Works like a charm.

I have actually never seen this syntax before, it sort of makes me feel like a newbie. ;)

Things should be as simple as possible, not simpler.

See here to know about few applications of APPLY operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources