SQL - query improvement MAX(date), IN

[Jorge]

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

[Greg]

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

[Greg]

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

[Erland Sommarskog]

Jorge (jo***************@gmail.com) writes:

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?

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-s...st-of-integers.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Jorge Delgado-Lopez]

Thank you for your answers.

The ids are actually Guids.

/ jorge delgado lopez

*** Sent via Developersdex http://www.developersdex.com ***