A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2008 yearSQL Query: Grouping by an ntext field - page 1
User InfoPosts
SQL Query: Grouping by an ntext field#1
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 :


Why is the desc_book (nvarchar) field being truncated when displayed in a PHP page?
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;

posted date: 2008-12-21 12:45:00


Re: SQL Query: Grouping by an ntext field#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2008-12-21 12:45:01


Re: SQL Query: Grouping by an ntext field#3
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).

posted date: 2008-12-21 13:04:00


Re: SQL Query: Grouping by an ntext field#4
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_soldFROM 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);

posted date: 2008-12-21 13:08:00


Re: SQL Query: Grouping by an ntext field#5
Excellent...tried and tested your solutions; both work perfectly.

posted date: 2008-12-21 13:25:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.