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.
table, the field
is of type
, and I'm sure that the problem is coming from there.
This is because before I changed the
, it was of type
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
and it 'solved my problem' (ie, the whole
was finally being displayed).
So basically these are my questions :
Why is the
(nvarchar) field being truncated when displayed in a PHP page?
How can i fix the SQL query to accommodate for grouping by an
Just for the record (which I don't think is very relevant), I am using MS SQL Server 2005
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
ON (bk.id_book = bc.id_book)
ORDER BY books_sold desc;
posted date: 2008-12-21 12:45:00