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

Author

Topic

craigmac999
Starting Member

14 Posts

Posted - 2007-12-31 : 21:02:10

Hi i have a sql sp as below, and i cant get it to group by the GroupMessageID, i want to show all records and group them by GroupMessageID, but i only want to show the cost, date etc of the first record.

so really i need to show all records and only show the first record in the GroupMessageID

SELECT SUM(Cost)AS TotCost, Date, ID, RCODE, MessageText, GroupMessageID
FROM Records
WHERE GroupMessageID <> 0
GROUP BY GroupMessageID, Cost, Date, ID, RCODE, MessageText
ORDER BY Date DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-01 : 01:23:01

Can you please post some sample data & expected o/p to illustrate what you are trying to achieve?

craigmac999
Starting Member

14 Posts

Posted - 2008-01-01 : 06:07:10

ok

DATABASE ROWS

ID, Cost, Date, MessageText, GroupMessageID

1, 2, 12/12/2007 12:47:01, TEST MESSGE, 2244
2, 2, 12/12/2007 12:47:02, TEST MESSGE, 2244
3, 2, 12/12/2007 12:47:03, TEST MESSGE, 2244
4, 2, 12/12/2007 12:47:04, TEST MESSGE, 2244
5, 1, 15/12/2007 12:47:50, NEXT MESSGE, 2245
6, 1, 15/12/2007 12:47:51, NEXT MESSGE, 2245

So what i want to show is

1, 2, 12/12/2007 12:47:01, TEST MESSGE, 2244
5, 2, 15/12/2007 12:47:50, NEXT MESSGE, 2245

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-01 : 08:38:46

If SQL 2005:-

SELECT tmp.ID, 
tmp.Cost, 
tmp.Date, 
tmp.MessageText, 
tmp.GroupMessageID
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY GroupMessageID ORDER BY ID) AS 'RowNo',
ID, 
Cost, 
Date, 
MessageText, 
GroupMessageID
FROM Records
)tmp
WHERE tmp.RowNo=1

In SQL 2000:-

SELECT tmp.ID, 
tmp.Cost, 
tmp.Date, 
tmp.MessageText, 
tmp.GroupMessageID
FROM
(
   SELECT t1.ID, 
   t1.Cost, 
   t1.Date, 
   t1.MessageText, 
   t1.GroupMessageID,
   (
     SELECT COUNT(*) + 1 
     FROM Records 
     WHERE GroupMessageID=t1.GroupMessageID
     AND ID <t1.ID
   ) AS 'RowNo'
   FROM Records t1
)tmp
WHERE tmp.RowNo=1

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2008-01-02 : 02:50:02

Hi!
Try this

select id,d.cost,d.date,d.messagetext,d.groupid from grouprows as e join
(select cost,min(date) as [date],messagetext,groupid from grouprows group by cost,messagetext,
groupid) as d on e.[date]=d.[date]

I think this will help You

kiruthika!
http://www.ictned.eu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-02 : 03:54:36

quote:
Originally posted by craigmac999

ok

DATABASE ROWS

ID, Cost, Date, MessageText, GroupMessageID

1, 2, 12/12/2007 12:47:01, TEST MESSGE, 2244
2, 2, 12/12/2007 12:47:02, TEST MESSGE, 2244
3, 2, 12/12/2007 12:47:03, TEST MESSGE, 2244
4, 2, 12/12/2007 12:47:04, TEST MESSGE, 2244
5, 1, 15/12/2007 12:47:50, NEXT MESSGE, 2245
6, 1, 15/12/2007 12:47:51, NEXT MESSGE, 2245

So what i want to show is

1, 2, 12/12/2007 12:47:01, TEST MESSGE, 2244
5, 2, 15/12/2007 12:47:50, NEXT MESSGE, 2245

I think you need to explain why uou want to get 2 for id 5?

Madhivanan

Failing to plan is Planning to fail

craigmac999
Starting Member

14 Posts

Posted - 2008-01-02 : 14:53:40

Hi visakh16

that did work, but just getting an error now because of the GroupMessageID as a varchar and i am getting a error of can not convert to int, any ideas why this is?

my database column is a varchar ?

i am using

SELECT tmp.ID,
tmp.Cost,
tmp.DateofSM,
tmp.MessageText,
tmp.GroupMessageID,
tmp.ClubID,
tmp.RCODE
FROM
(
SELECT t1.ID,
t1.Cost,
t1.DateofSM,
t1.ClubID,
t1.MessageText,
t1.GroupMessageID,
t1.RCODE,
(
SELECT COUNT(*) + 1
FROM SMS_Records
WHERE GroupMessageID=t1.GroupMessageID
AND ID <t1.ID
) AS 'RowNo'
FROM SMS_Records t1
)tmp
WHERE tmp.RowNo=1 AND GroupMessageID <> 0

AND DATENAME(month, DateOfSM)= @Date
AND ClubID = @ClubID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 23:21:55

quote:
Originally posted by craigmac999

Hi visakh16

that did work, but just getting an error now because of the GroupMessageID as a varchar and i am getting a error of can not convert to int, any ideas why this is?

my database column is a varchar ?

i am using

SELECT tmp.ID,
tmp.Cost,
tmp.DateofSM,
tmp.MessageText,
tmp.GroupMessageID,
tmp.ClubID,
tmp.RCODE
FROM
(
SELECT t1.ID,
t1.Cost,
t1.DateofSM,
t1.ClubID,
t1.MessageText,
t1.GroupMessageID,
t1.RCODE,
(
SELECT COUNT(*) + 1
FROM SMS_Records
WHERE GroupMessageID=t1.GroupMessageID
AND ID <t1.ID
) AS 'RowNo'
FROM SMS_Records t1
)tmp
WHERE tmp.RowNo=1 AND CAST(GroupMessageID AS int) <> 0

AND DATENAME(month, DateOfSM)= @Date
AND ClubID = @ClubID

Are you telling GroupMessageID is of type varchar?

then change like above..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources