group by issue - 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

General SQL Server Forums

New to SQL Server Programming

group by issue

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-03-11 : 04:50:56

Hi i have a sql query, where i just want to group by GroupMessageID

but i am getting aggregate function error i can not add anymore columns to the group by as that effects my output

SELECT cost, Users_Date_Sent, RCODE, MessageText, GroupMessageID
FROM dbo.sms_records
GROUP BY GroupMessageID

any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 04:55:34

Do you know what aggregation is?
What is the purpose of the query?

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 05:01:16

maybe the opposite direction?

SELECT cost, Users_Date_Sent, RCODE, MessageText, max(GroupMessageID) as GroupMessageID
FROM dbo.sms_records
GROUP BY cost, Users_Date_Sent, RCODE, MessageText

Please read this blog post
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-03-11 : 05:48:30

Hi ok i tried that but again my results are wrong

i have 2 or 3 GroupMessageID that are the same

this is what i need
cost, Users_Date_Sent, RCODE, MessageText, GroupMessageID

and then group all by GroupMessageID (so only one unique GroupMessageID will appear)

thought it should just be a standard group by clause???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 05:54:16

Sinve you didn't bother to answer my question wether you are using SQL Server 2000 or SQL Server 2005 I will post a SQL Server 2005 solution.

SELECT	Cost,
	Users_Date_Sent,
	RCODE,
	MessageText,
	GroupMessageID
FROM	(
		SELECT	Cost,
			Users_Date_Sent,
			RCODE,
			MessageText,
			GroupMessageID,
			ROW_NUMBER() OVER (PARTITION BY GroupMessageID ORDER BY Users_Date_Sent DESC) AS RecID
		FROM	dbo.sms_records
	) AS d
WHERE	RecID = 1

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-11 : 07:11:02

craigmacca -- be sure to read this carefully:

http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 07:17:47

Please specify your exact requirements for helping somebody who's ready to provide you with quick solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources