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