I need to retrieve the count of repetitive rows(ntext data type) in a table and the row data. This column contains Chinese and Japanese characters. These characters appear like question marks once cast to nvarchar(max). So if use group by on this column, its getting grouped with respect to the question marks when I need these to be grouped according to the Chinese or Japanese messages. My query is as follows.
SELECT Message, COUNT(*) TotalCount
FROM ipi_messagecustomization
GROUP BY CAST([message] AS varchar(max)) WITH ROLLUP
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
Kindly help. thanks all !!