Incorrect syntax near the keyword SELECT

Hello, I have the following query. When I run the query I get the following error message: "Incorrect syntax near keyword SELECT"

----------------
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM
SELECT TOP (100) PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard,
Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hits
FROM Videos LEFT OUTER JOIN
(SELECT TOP (100) PERCENT VideoId, COUNT(*) AS hits
FROM (SELECT TOP (100) PERCENT UserId, VideoId, COUNT(*) AS cnt1
FROM Hits
GROUP BY VideoId, UserId) AS TB1
GROUP BY VideoId) AS TB2 ON Videos.VideoId = TB2.VideoId
ORDER BY TB2.hits DESC
) AS T1
WHERE rownum <= 5
-------------

If I run the query that is in BOLD as:

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM Videos) AS T1 WHERE rownum <=5

the query runs just fine. Also if I run the query that is NOT bold (above), it also runs fine. What can I do to run them both together as seen above?

Thank in advance,

Louis

Try this:

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM(SELECT TOP (100) PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard,
Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hits
FROM Videos LEFT OUTER JOIN
(SELECT TOP (100) PERCENT VideoId, COUNT(*) AS hits
FROM (SELECT TOP (100) PERCENT UserId, VideoId, COUNT(*) AS cnt1
FROM Hits
GROUP BY VideoId, UserId) AS TB1
GROUP BY VideoId) AS TB2 ON Videos.VideoId = TB2.VideoId
ORDER BY TB2.hits DESC

)
) AS T1
WHERE rownum <= 5

|||

Thanks Limno, but I had tried that before and I get this error:

Incorrect syntax near ')'

I don't understand why

Louis

|||

Here is a working one:

SELECT*FROM(SELECT ROW_NUMBER()OVER(ORDERBY DateAdded)AS rownum,*FROM(SELECTTOP(100)PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard,

Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hits

FROM VideosLEFTOUTERJOIN

(SELECTTOP(100)PERCENT VideoId,COUNT(*)AS hits

FROM(SELECTTOP(100)PERCENT UserId, VideoId,COUNT(*)AS cnt1

FROM Hits

GROUPBY VideoId, UserId)AS TB1

GROUPBY VideoId)AS TB2ON Videos.VideoId= TB2.VideoId

ORDERBY TB2.hitsDESC

)AS T1) AS T2

WHERE rownum<= 5

|||

Thanks Limno, that worked

Louis