sql server new users: 'Group by' problem -- sql server new users

You're in the

sql server new users

group:

'Group by' problem

Digit24
12/21/2005 4:39:57 PM

sql server new users:

This is an SQL statement that we had working in an Access database:

Select First(tblProducts.ProductName) AS TheProduct, First(TheDescription)
AS TheDescriptionText, tblProducts.ProductID, First(tblVersions.TheMedium)
AS TheMedium, First(tblProducts.TheOrder) AS TheOrder FROM tblProducts LEFT
JOIN tblVersions ON tblProducts.ProductID = tblVersions.ProductID where
(tblProducts.TheDisplay = TRUE and tblVersions.TheDisplay = True) and
(tblProducts.ProductID = 1) GROUP BY tblProducts.ProductID Order by
First(tblProducts.TheOrder)

I changed this for SQL Server to:

Select Max(tblProducts.ProductName) AS TheProduct, Max(TheDescription) AS
TheDescriptionText, tblProducts.ProductID, Max(tblVersions.TheMedium) AS
TheMedium, Max(tblProducts.TheOrder) AS TheOrder FROM tblProducts LEFT JOIN
tblVersions ON tblProducts.ProductID = tblVersions.ProductID where
(tblProducts.TheDisplay = 1and tblVersions.TheDisplay = 1) and
(tblProducts.ProductID = 1) GROUP BY tblProducts.ProductID Order by
Max(tblProducts.TheOrder)

This has got rid of the error about First not being a recognised function.
However, it is still pulling up results as the fields ProductName and
TheDescription are ntext and it says that these can not be used in an
agregate function. I have tried changing them to another data type but it
won't let me.

Any ideas?

Thanks in advance

Re: 'Group by' problem

Hugo Kornelis
12/21/2005 11:29:32 PM

[quoted text, click to view]

Hi Digit24,

What does "won't let me" mean? Are you trying to change the data type in
your tables, or only in this query?

At least for ProductName, you should change this in the table. I can't
imagine a product with a name that exceeds 4,000 characters. For the
description, you'll have to decide if the maximum length is less than or
more than 4,000 characters.

Anyway, to get this query working, just CAST to nvarchar(4000) (or,
preferably, shorter):

Select Max(CAST(tblProducts.ProductName AS nvarchar(4000))) AS
TheProduct, Max(CAST(TheDescription AS nvarchar(4000))) AS
TheDescriptionText, tblProducts.ProductID, Max(tblVersions.TheMedium) AS
TheMedium, Max(tblProducts.TheOrder) AS TheOrder FROM tblProducts INNER
JOIN
tblVersions ON tblProducts.ProductID = tblVersions.ProductID where
(tblProducts.TheDisplay = 1and tblVersions.TheDisplay = 1) and
(tblProducts.ProductID = 1) GROUP BY tblProducts.ProductID Order by
Max(tblProducts.TheOrder)

As you see, I also changed the LEFT outer JOIN to an INNER JOIN. Since a
column from the right table was referenced in the WHERE clause, the net
effect will be the same as an inner join anyway, only (possibly) slower.

The query above is untested. See www.aspfaq.com/5006 if you prefer a
tester reply.

Best, Hugo
--

Re: 'Group by' problem

Digit24
12/22/2005 9:57:39 AM

Thanks for that - works perfectly!

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.

home · blog · groups

Questions? Comments? Contact the dn