Wrox Blogs

norman001 · February 16th, 2009, 11:52 PM

Hi,

I am working on adding functionality to search blogs from BlogEntry table.
I would like to select top 20 rows along with count of number of rows it is selecting.
The following SQL (MS SQL 2000) statement gives me errors.
What am I doing wrong here?

SELECT TOP 20 Id, Title, Body,
CategoryID, DatePublished, PostedBy, CommentCount, searchkeywords, isenabled, count(ID) as BlogCount
FROM BlogEntry WHERE isEnabled=1
GROUP BY DatePublished DESC

(This is part of a long dynamic SQL statement...for simplicity I am posting only part that gives me problems..)

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'DESC'.

Imar · February 17th, 2009, 06:08 AM

GROUP BY doesn't take a sorting direction; ORDER BY does...

GROUP BY DatePublished DESC

should be

ORDER BY DatePublished DESC

Imar

norman001 · February 17th, 2009, 10:06 AM

Thanks! I changed the GROUP BY to ORDER BY then I get the following errors..

SELECT TOP 20 Id, Title, Body,
CategoryID, DatePublished, PostedBy, CommentCount, searchkeywords, isenabled, count(ID) as BlogCount
FROM BlogEntry WHERE isEnabled=1
ORDER BY DatePublished DESC

ERRORS:
Server: Msg 8118, Level 16, State 1, Line 1
Column 'BlogEntry.CommentCount' is invalid in the select list because it is
not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'BlogEntry.searchkeywords' is invalid in the select list because it is
not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'BlogEntry.isenabled' is invalid in the select list because it is
not contained in an aggregate function and there is no GROUP BY clause.

Imar · February 17th, 2009, 01:52 PM

If you do a COUNT, you also need to use a GROUP BY:

SELECT TOP 20 Id, Title, Body,
CategoryID, DatePublished, PostedBy, CommentCount, searchkeywords, isenabled, count(ID) as BlogCount
FROM BlogEntry WHERE isEnabled=1
GROUP BY Id, Title, Body,
CategoryID, DatePublished, PostedBy, CommentCount, searchkeywords, isenabled
ORDER BY DatePublished DESC

However, this may result in a few more problems. First, the WHERE clasue. You may need to replace it with a HAVING clause:

HAVING isEnabled=1

Secondly, the Body. Depending on the underlying data type (text for example), you can't use it in a GROUP by clause.

Finally, what does this query do? What's the point of returning the count of the table's ID?

You may want to look up GROUP BY in the SQL Server Books Online. It shows you a number of useful examples.

Cheers,

Imar