select text field in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select text field in query

Author

Topic

deppi
Starting Member

3 Posts

Posted - 2004-08-26 : 07:07:48

HI,

How do I include a text (or ntext) field in a query that uses an aggregate function?
I could not use text field in the group by clause. How do I do this in SQL Server?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-26 : 07:55:59

you can't. you'll need to use a select with out the text field, and the join it to another select in which you pick the text field you want to use.

Go with the flow & have fun! Else fight the flow :)

Desmag
Starting Member

18 Posts

Posted - 2006-02-02 : 11:40:18

I have a similar problem. I need to count user's posts, but this means that I have to GROUP BY which is impossible because one of the columns is nText.

Spirit1 mention join with another select. Is this the right approach?:

SELECT TOP 1

P1.Post_ID,
P1.Post_Title,
P1.Post_Author_Id,
P1.Post_Date,
P1.Post_Category,
P1.Visible,
COUNT(P1.Post_ID) AS Users_replies,
P2.Post_Body

FROM tbl_Forum_Post P1
INNER JOIN tbl_Forum_Post P2 ON
P1.Post_Id = P2.Post_Id

WHERE P1.Post_Id = @Thread
And P1.Visible = 1

GROUP BY
P1.Post_ID,
P1.Post_Title,
P1.Post_Author,
P1.Post_Date,
P1.Post_Category,
P1.Visible

stephe40
Posting Yak Master

218 Posts

Posted - 2006-02-02 : 11:56:06

I am guessing you want to display the post and the a count of how many user replies were made for that post. Try this:


select P1.Post_ID, P1.Post_Title, P1.Post_Author_Id, P1.Post_Date, P1.Post_Category, P1.Visible, p2.cnt
from tbl_Forum_Post P1 inner join 
  (select px.postID, count(*) as cnt from tbl_Forum_Post px group by px.postID) p2 
  on p1.postId = p2.postID
WHERE P1.Post_Id = @Thread And P1.Visible = 1

- Eric

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-03 : 03:30:04

If you want to include text column in Group by you need to convert that to varchar datatype which may not give you perfect result

Madhivanan

Failing to plan is Planning to fail

Desmag
Starting Member

18 Posts

Posted - 2006-02-07 : 20:02:18

Thanks guys!

stephe40 - your method solved my problem :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources