Hi,
I have a need to display the Top 4 of one table based on its amount of
related rows in another table.
i.e
Members Table
MemberID
MemberName
Logins Table
MemberID
DateTimeLogin...
Currently I have code that basically does this:
SELECT TOP 4 MemberName,
(SELECT TOP 1 Count(Members.LoginID) AS TheCount
FROM Logins WHERE Members.MemberID = Members.MemberID AND DateTimeLogin
BETWEEN @dtFrom AND getDate() ORDER BY TheCount DESC) AS TotalLogins
FROM Members
WHERE MemberActive = 1
ORDER BY TotalLogins DESC
This works fine for small amounts... But now the DB has a few rows in it, it
is really taking its time to do...
Can anyone suggest a better way of getting this out of the DB?
TIA
Mikey
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
