SQL Query Filter for search?

Question

Below is my sql query. I have 4 versions for "about-us" pages stored in database but i would like to display one version with the last modified date. Please help me out how can i achive this(with out using TOP 1 & Distinct).

SELECT Pages.PageId,Pages.Title, Pages.Url, PageContent.PageId, PageContent.Published, PageContent.Content FROM Pages
INNER JOIN PageContent on PageContent.PageId=Pages.PageId
WHERE Pages.Title LIKE @0 OR PageContent.Content LIKE @0 OR Pages.Url LIKE @0 AND
PageContent.Published < GetDate() ORDER BY PageContent.Published DESC

Answer 1

SELECT Pages.PageId,Pages.Title, Pages.Url, PageContent.PageId, A.Published, PageContent.Content FROM Pages



INNER JOIN (SELECT PageID, MAX(Published) FROM PageContent GROUP BY PageID) A ON A.PageID = PageContent.PageID



WHERE Pages.Title LIKE @0 OR PageContent.Content LIKE @0 OR Pages.Url LIKE @0 AND

A.Published < GetDate()



---



Tips to share:



Putting a SUBQUERY inside a WHERE Clause, makes your query run slower... ^_^

Answer 2

Hello Vikram,



SQL Server has introduced Row_Number() function with SQL Server 2005 version.

This is a great ranking function just like others that developers use a lot for such requirements.



Here is how you can use Row_Number() and CTE expression in a single SELECT query



WITH CTE as (

SELECT

RN = ROW_NUMBER() OVER (Order By PageContent.Published DESC),

Pages.PageId,

Pages.Title,

Pages.Url,

PageContent.PageId,

PageContent.Published,

PageContent.Content

FROM Pages

INNER JOIN PageContent on PageContent.PageId=Pages.PageId

WHERE

Pages.Title LIKE @0 OR

PageContent.Content LIKE @0 OR

Pages.Url LIKE @0 AND

PageContent.Published < GetDate()

ORDER BY PageContent.Published DESC

)

select * from CTE where RN = 1



I hope that helps,

Answer 3

I am not very good at SQL but I think a "sub query" will work.



Assuming something like:

SELECT MAX(DATE) FROM tablewithdatein

will get the latest entry date, then you need to add that to your existing WHERE statement:

...

WHERE Pages.Title LIKE @0 OR PageContent.Content LIKE @0 OR Pages.Url LIKE @0 AND

PageContent.Published < GetDate() ORDER BY PageContent.Published DESC

AND table.date = (SELECT MAX(DATE) FROM tablewithdatein)