Help With Select Statement

My Current Query:

select rpg.rpg_sortorder,rpg.rpg_groupname,
act.act_cardprocid, sum(act.act_trxamtn) as Amount from
actlog act
right outer join
report_groups rpg on rpg.rpg_groupcode = act.act_CardProcID
WHERE
(rpg.rpg_report = 'SS') AND
(rpg.rpg_groupname <> 'not on report')
group by rpg.rpg_groupname, rpg.rpg_sortorder, act.act_cardprocid
order by rpg_sortorder

Report_groups looks like this

rpg_sortorder rpg_groupname rpg_groupcode
2 debit cards db
3 discover ds
4 visa vs
8 food stamps ef
10 gift cards gc
14 fleet cards wx
15 fleet cards mf
16 fleet cards vy
17 ach ac

Actlog looks like this:

act_cardprocid Amount
db 25.00
db 25.00
vs 100.00
vs 200.00

resultset I wish to achieve

rpg_sortorder rpg_groupname act_cardprocid Amount
2 debit cards db 50.00
3 Discover null null
4 Visa vs 300.00
8 food stamps null null
10 gift cards null null
14 Fleet Cards null null
17 ach null null

Note that in the join, I only need one record to represent group name and sortorder.
If there happens to be three records in report_groups for the same groupname, I only want
the top record. Hence, I do NOT want the following to showup in my results:

15 Fleet cards null null
16 Fleet cards null null

How can I filter out these unwanted records?

This seems to produce your desired output.

Code Snippet

SET NOCOUNT ON

DECLARE @.Report_Groups table
( Rpg_SortOrder int,
Rpg_GroupName varchar(20),
Rpg_GroupCode char(2)
)

INSERT INTO @.Report_Groups VALUES ( 2, 'debit cards', 'db' )
INSERT INTO @.Report_Groups VALUES ( 3, 'discover', 'ds' )
INSERT INTO @.Report_Groups VALUES ( 4, 'visa', 'vs' )
INSERT INTO @.Report_Groups VALUES ( 8, 'food stamps', 'ef' )
INSERT INTO @.Report_Groups VALUES ( 10, 'gift cards', 'gc' )
INSERT INTO @.Report_Groups VALUES ( 14, 'fleet cards', 'wx' )
INSERT INTO @.Report_Groups VALUES ( 15, 'fleet cards', 'mf' )
INSERT INTO @.Report_Groups VALUES ( 16, 'fleet cards', 'vy' )
INSERT INTO @.Report_Groups VALUES ( 18, 'ach', 'ac' )

DECLARE @.Actlog table
( Act_CardProcID char(2),
Act_TrxAmtn decimal(10,2)
)

INSERT INTO @.ActLog VALUES ( 'db', 25.00 )
INSERT INTO @.ActLog VALUES ( 'db', 25.00 )
INSERT INTO @.ActLog VALUES ( 'vs', 100.00 )
INSERT INTO @.ActLog VALUES ( 'vs', 200.00 )

SELECT
SortOrder = min( r.Rpg_SortOrder ),
GroupName = r.Rpg_GroupName,
CardProdID = min( a.Act_CardProcID ),
Amount = sum( a.Act_TrxAmtn )
FROM @.Report_Groups r
LEFT JOIN @.ActLog a
ON r.Rpg_GroupCode = a.Act_CardProcID
GROUP BY r.Rpg_GroupName
ORDER BY SortOrder

SortOrder GroupName CardProdID Amount
-- -- -
2 debit cards db 50.00
3 discover NULL NULL
4 visa vs 300.00
8 food stamps NULL NULL
10 gift cards NULL NULL
14 fleet cards NULL NULL
18 ach NULL NULL