SQL Query: Grouping by an ntext field

Question

I have the below query, which basically it retrieves the 5 top most books sold:

    select top 5 count(id_book_orddetails) 'books_sold', bk.*
    from orderdetails_orddetails ord inner join books_book bk
    on ord.id_book_orddetails = bk.id_book
    group by id_book, name_book,author_book,desc_book,id_ctg_book,qty_book,image_book,isdeleted 
    order by 'books_sold' desc

The problem is that I am receiving this error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

In the books_book table, the field desc_book is of type ntext, and I'm sure that the problem is coming from there.

This is because before I changed the desc_book to ntext, it was of type nvarchar and it worked perfectly.

The reason I changed the data type of this field is because somehow in PHP website, when I was displaying the book description (a different sp), the description was being truncated to about 200-255 characters, thus I changed it to ntext and it 'solved my problem' (ie, the whole desc_book was finally being displayed).

So basically these are my questions :

1. Why is the desc_book (nvarchar) field being truncated when displayed in a PHP page?
2. How can i fix the SQL query to accommodate for grouping by an ntext field?

Just for the record (which I don't think is very relevant), I am using MS SQL Server 2005

[UPDATE]

I tried and tested both of Bill Karwin's proposed solutions and they both work perfectly. I thus decided in grouping the count aggregate result into a subquery...ie's Karwin's latter solution.

So here is my updated (fully working) statement:

SELECT bk.*, bc.books_sold
FROM books_book bk
INNER JOIN (
    SELECT bk2.id_book, COUNT(*) books_sold
    FROM books_book bk2 
    INNER JOIN orderdetails_orddetails ord 
    ON (bk2.id_book = ord.id_book_orddetails)
    GROUP BY bk2.id_book
) bc
ON (bk.id_book = bc.id_book)
ORDER BY books_sold desc;

Answer 1

First of all, don't use bk.*, use the full column list of the columns you actually need.

I can't answer question 1, but here's an answer to question 2: instead of select and grouping on desc_book, cast it like this instead: CAST(desc_book AS NVARCHAR(2000)) AS desc_book (or other appropriate size of the nvarchar).

Answer 2

The old PHP "mssql" extension only supports VARCHAR up to 255 bytes in size. This is a known limitation, and it's why Microsoft has been developing a new PHP extension to support modern SQL Server releases.

One workaround is to declare the storage of that column as NVARCHAR, but when you query it from PHP, use CAST to convert it to NTEXT. Then the full length can be returned.

Another option is to keep the column stored as NTEXT, but GROUP BY only book_id by putting the count into a subquery:

SELECT bk.*, bc.books_sold
FROM books_book bk
  INNER JOIN (SELECT bk2.book_id, COUNT(*) books_sold
      FROM books_book bk2 INNER JOIN orderdetails_orddetails ord 
        ON (bk2.id_book = ord.id_book_orddetails)
      GROUP BY bk2.book_id) bc
    ON (bk.book_id = bc.book_id);