SQL - query improvement MAX(date), IN

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jorge - 28 Apr 2005 21:48 GMT

Hi,
I wonder if you could shed some light into this.

I have the following table.
Id, ContentId, VersionDate, ContentXml

There are several ContentIds in the table.

SELECT *
FROM tblVersions
WHERE (VersionDate =
(SELECT MAX(tblVersions2.VersionDate)
FROM tblContentVersion AS
tblVersions2
WHERE tblVersions2.ContentiD =
tblVersions.ContentiD))
ORDER BY ContentId

This query works to select the latest versions (MAX) of every content,
but I do not like it, any other way to do this properly?

I also want to do this knowing a set of ids (probably using IN )

SELECT *
FROM tblVersions
WHERE (VersionDate =
(SELECT MAX(tblVersions2.VersionDate)
FROM tblContentVersion AS
tblVersions2
WHERE tblVersions2.ContentiD =
tblVersions.ContentiD AND tblVersions.ContentiD IN (1, 2, 3, 6, 7, 8)
))
ORDER BY ContentId

Any ideas for improvements on this query?

ContentXml is of ntext type

Thanks,
/ jorge

Reply to this Message

Greg - 28 Apr 2005 21:56 GMT

Hiya Jorge,

The corelated subquery is a good solution - although depending on
tablesizes it might not be optimal (subquery is executed once for
everyrow returned in the parent query). I'd suggest to check the
indexes and stats first. Secondaly you could try the following..

SELECT
    *
FROM tblVersions a join
    (select
        contentid,
        MAX(versionDate) as max_versiondate
    from
        tblContentVersion
    group by
        contentid)dt
    where a.contentid=dt.contentid and
    a.versiondate=dt.max_versiondate

Reply to this Message

Greg - 28 Apr 2005 21:59 GMT

Sorry type'o on the query..

SELECT
*
FROM tblVersions a join
(select
contentid,
MAX(versionDate) as max_versiondate
from
tblContentVersion
group by
contentid)dt
ON a.contentid=dt.contentid and
a.versiondate=dt.max_versiondate

Reply to this Message

Erland Sommarskog - 28 Apr 2005 22:32 GMT

> I also want to do this knowing a set of ids (probably using IN )
>
[quoted text clipped - 10 lines]
>
> Any ideas for improvements on this query?

A good alternative to the subquery is to use derived table as Greg
suggested. Then you can easily add a filer on the id:s

SELECT *
FROM tblVersions a
join (select contentid,
MAX(versionDate) as max_versiondate
from tblContentVersion
group by contentid) dt
ON a.contentid=dt.contentid and
a.versiondate=dt.max_versiondate
WHERE a.ContentID IN (1, 2, 3, 6, 7, 8)

If you want to pass the list of ids as a parameter, check out
http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-integers.

Signature

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Reply to this Message