Removing Duplicates from a query

So I have the following query

SELECT r.userId, r.programmeId, u.email, u.firstName, u.lastName, u.profileId, tblProgrammes.name, u.storeId, r.dateEnded AS lastPass, r.dateExpired
FROM tblUserRepeatHistory AS r INNER JOIN
tblUsers AS u ON r.userId = u.id INNER JOIN
tblProgrammes ON r.programmeId = tblProgrammes.id
WHERE (r.date开发者_如何转开发Expired IS NOT NULL) AND (u.storeId = @storeId)
GROUP BY r.userId, r.programmeId, u.email, u.firstName, u.lastName, u.profileId, tblProgrammes.name, u.storeId, r.dateEnded, r.dateExpired, r.id
HAVING (DATEDIFF(D, MAX(r.dateExpired), GETDATE() + 31) >= 0)

Which returns the following data

11 22 asdf@asdf.com store-mgr jamie2 1 Deli Food Service 1 5 04/02/2011 09:36:11 10/05/2011 09:36:11

11 22 asdf@asdf.com store-mgr jamie2 1 Deli Food Service 1 5 04/02/2011 09:36:11 10/05/2011 09:36:11

11 22 asdf@asdf.com store-mgr jamie2 1 Deli Food Service 1 5 11/05/2011 09:44:36 10/07/2011 09:44:36

11 23 asdf@asdf.com store-mgr jamie2 1 Deli Food Service 2 5 11/05/2011 10:12:50 16/06/2011 10:12:50

I basically only 1 record returned for each user and programme, where the most recently added one should be returned so i guess by MAX(dateExpired). So i.e this query should only return two records

11 22 asdf@asdf.com store-mgr jamie2 1 Deli Food Service 1 5 11/05/2011 09:44:36 10/07/2011 09:44:36
11 23 asdf@asdf.com store-mgr jamie2 1 Deli Food Service 2 5 11/05/2011 10:12:50 16/06/2011 10:12:50

Remove r.dateExpired from GROUP BY clause and replace it with "MAX(r.dateExpired) as dateExpired" in select list.

SELECT DISTINCT are the SQL keywords you'll need. DISTINCT causes the query to only return the columns that are different

you can use cross apply

SELECT * FROM tblUsers as u CROSS JOIN tblProgrammes p
CROSS APPLY
(SELECT TOP 1 dateExpired FROM tblUserRepeatHistory
WHERE userId = u.id and programmeId = p.id
ORDER BY dateExpired desc
) as rr

in the co-related sub query you get the maximum dateExpired in tblUserRepeatHistory where meet the programme and the user, cross apply is something like inner join, for nested queries and table-valued functions