GROUP BY a text column

Question

GROUP BY a text column

Ben Hughes

Valued Member

Points: 71
More actions
August 18, 2004 at 4:29 am

#163111

I'm trying to build a shopping cart item listing query whereby the items are in a one-level heirarchy (an item can be tied to another item). I can make it work by doing a self join, but I need to be able to group the results and unlike MySQL, which I am used to working with, every non-aggregate item in the select clause needs to be in the group clause. This causes a problem because SQL server is throwing an error when I try to group by a text column in the select clause that I need:
"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
.. yet when I try to group only by the cart item's ID, I get the error about requiring all non-aggregate select items to be grouped.
Although I know it's difficult to understand without knowing my data schema, any help would be most appreciated:
DECLARE @ID INT
SET @ID = 1
SELECT
ci.ID, ci.Price, ci.Quantity, ci.ParentID, ci.CartItemDate, ci.IsAvailable,
et.Prefix, et.Name AS EntityTypeName,
i.ID AS IssueID, i.Header, i.Subheader, SUBSTRING(i.Text, 0, 30) AS [Text], i.IsGeneric,
el.ID AS EbayListingID, el.Title AS EbayListingTitle, el.EbayNumber,
r.ID AS ResourceID, r.Name AS ResourceName,
-- Item Code
CASE et.Name
WHEN 'Issue' THEN (ISNULL(et.Prefix, '') + CAST(i.ID AS VARCHAR(20)))
WHEN 'Ebay Item' THEN (ISNULL(et.Prefix, '') + CAST(el.EbayNumber AS VARCHAR(20)))
WHEN 'Birthday Issue' THEN (ISNULL(et.Prefix, '') + CAST(DATEPART(yyyy, ci.CartItemDate) AS CHAR(4)) + RIGHT('00' + CAST(DATEPART(mm, ci.CartItemDate) AS VARCHAR(2)), 2) + RIGHT('00' + CAST(DATEPART(dd, ci.CartItemDate) AS VARCHAR(2)), 2))
WHEN 'Resource' THEN (ISNULL(et.Prefix, '') + CAST(r.ID AS VARCHAR(30)))
END AS ItemCode,
-- Item Text
CASE et.Name
WHEN 'Issue' THEN (SUBSTRING(i.Text, 0, 30))
WHEN 'Ebay Item' THEN (el.Title)
WHEN 'Birthday Issue' THEN 'Birthday Issue ' + (CAST(DATEPART(yyyy, ci.CartItemDate) AS CHAR(4)) + '-' + RIGHT('00' + CAST(DATEPART(mm, ci.CartItemDate) AS VARCHAR(2)), 2) + '-' + RIGHT('00' + CAST(DATEPART(dd, ci.CartItemDate) AS VARCHAR(2)), 2))
WHEN 'Resource' THEN (r.Name)
END AS ItemText,
-- Item URL
CASE et.Name
WHEN 'Issue' THEN ('/admin/inventory/view.aspx?id=' + CAST(i.ID AS VARCHAR(20)))
WHEN 'Ebay Item' THEN ('/admin/inventory/ebay/view.aspx?id=' + CAST(el.ID AS VARCHAR(20)))
WHEN 'Birthday Issue' THEN '#'
WHEN 'Resource' THEN '#'
END AS ItemURL
FROM CartItems ci
LEFT OUTER JOIN CartItems ci2 ON ci.ID = ci2.ParentID -- Self-Join for Heirarchy
INNER JOIN EntityTypes et ON et.ID = ci.EntityTypeID
LEFT OUTER JOIN Issues i ON ci.EntityID = i.ID AND et.Name = 'Issue'
LEFT OUTER JOIN EbayListings el ON ci.EntityID = el.ID AND et.Name = 'Ebay Item'
LEFT OUTER JOIN Resources r ON ci.EntityID = r.ID AND et.Name = 'Resource'
WHERE
ci.OrderID = @ID
GROUP BY
ci.ID, ci.Price, ci.Quantity, ci.ParentID, ci.CartItemDate, ci.IsAvailable,
et.Prefix, et.Name,
i.ID, i.Header, i.Subheader, i.Text, i.IsGeneric,
el.ID, el.Title, el.EbayNumber,
r.ID, r.Name

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Have you thought about doing a CAST or CONVERT of the TEXT field to be a NVARCHAR or VARCHAR that you would then be able to GROUP BY?? These fields hold 4000/8000 characters respectively and hopefully would provide enough of the description to be useful?

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Ben Hughes Valued Member Points: 71 More actions · Answer 2

No, it just doesn't work that way. It doesn't matter what you do to the column in the select clause, the GROUP BY clause considers is TEXT, and you can't cast or convert it there either. If there isn't a solution to this, I'll be very dissapointed in SQL Server - What a stupid issue. MySQL or PostgreSQL would handle this easily, but the supposed power of SQL Server can't even handle this simple construct.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

Have you tried doing the CAST/CONVERT in BOTH the SELECT and GROUP BY??

I have had to do this before for various other reasons and it should work (I think) 🙂

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Very simplified down

i.ID, i.Header, i.Subheader, CAST(i.Text AS VARCHAR(200)) from Issues i

group by i.ID, i.Header, i.Subheader, CAST(i.Text AS VARCHAR(200))

Increase the size of the varchar if you need more chars (up to max of 8000)

Out of interest, why are you using grouping? I can't see any aggregation been done anywhere in the query. If you're looking for distinct values then rather use

SELECT DISTINCT {field list} FROM ....

You'll still have to cast the text to varchar for distinct though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luigi Visintin SSC Eights! Points: 822 More actions · Answer 5

As a workaround you could use an aggregate function on the text column in the SELECT list, as:

SELECT .... ,.., MIN(TextColumn) AS TxtCol, ....

this way the text column can be omitted from the GROUP BY...

PS: I don't know if this column is such to be unique after the GROUP BY of all other columns.

HTH

Luigi