| Author |
 Topic  |
|
|
Drew Westling
Starting Member
Sweden
8 Posts |
 Posted - 04/13/2012 : 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
Sweden
29138 Posts |
Posted - 04/13/2012 : 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
Sweden
8 Posts |
Posted - 04/13/2012 : 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
India
47189 Posts |
Posted - 04/13/2012 : 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/
|
|
|
| |
Topic |
|
Inner join with top x rows.
