Posted by Erland Sommarskog on 08/29/06 21:31

(steven.fafel@gmail.com) writes:
> SELECT licenseKey, (
> SELECT TOP 1 mi.id FROM messages mi
> INNER JOIN identities i ON i.id=mi.identityid
> INNER JOIN licenses l on i.licenseid=l.id
> WHERE l.licenseKey = t1.licenseKey AND category = 'usage'
> ORDER BY mi.created DESC
> ) as messageid
> FROM licenses T1

Correlated subqueries in the SELECT list is rarely good for performance.
Try:

SELECT m.licenseKey, m.id
FROM messages m
JOIN (SELECT l.licenseKey, maxcreated = (mi.created)
FROM messages mi
JOIN identities i ON i.id=mi.identityid
JOIN licenses l on i.licenseid=l.id
GROUP BY l.licenseKey) AS m1 ON m.created = m1.maxcreated

This may or may not yield the same result, depending on how unique
messages.created is. Also, I'm assuming here that every license will
have a match in identities/messages.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Is there a way to transfer ntext data from one table to another? MSSQL2000
• Prev in forum: Re: Tough Correlated Subquery issue
• Thread view: Re: Tough Correlated Subquery issue

[Reply to this message]