Need help with SELECT query please!

[tbolt]

I have a problem selecting unique rows from a table where if two specific columns are the same value as another row. Easier if I lay it out:

Table Notifications (other columns, but not useful to example)
id, profile_id, recipient_id, created, type, type_id, notify

I only want to return * on rows that are truly unique on profile_id and type so if I had this:

1, 4, 3, 2009-07-09 05:32:15, message, 0, 0
1, 2, 8, 2009-07-12 21:22:47, comment, 1, 12
1, 5, 2, 2009-07-16 13:45:17, message, 0, 0
1, 5, 8, 2009-07-21 17:46:34, comment, 1, 12
1, 5, 8, 2009-07-23 14:39:03, edit, 2, 7
1, 5, 8, 2009-07-24 20:41:45, comment, 1, 12

Where 'recipient_id = 8' I only want to return:

1, 4, 3, 2009-07-09 05:32:15, message, 0, 0
1, 2, 8, 2009-07-12 21:22:47, comment, 1, 12
1, 5, 2, 2009-07-16 13:45:17, message, 0, 0
1, 5, 8, 2009-07-21 17:46:34, comment, 1, 12
1, 5, 8, 2009-07-23 14:39:03, edit, 2, 7
1, 5, 8, 2009-07-24 20:41:45, comment, 1, 12

[stephen84s]

Since your records are and will be truly unique by date (as you have mentioned in the edit comment), you could use a simple join query like this:-

SELECT A.id, A.profile_id, A.recipient_id, A.created, A.type, A.type_id, A.notify
FROM Notifications A, Notifications B
WHERE A.receipient_id = B.receipient_id AND 
A.profile_id = B.profile_id AND
A.type = B.type AND
A.receipient_id = 8 AND
A.created >= B.created

Note:-
I have only mentally evaluated the query, could not test it as I am not close to any mysql server right now.

[tbolt]

I figured it out last night actually. It was just a matter of a simple GROUP BY like this:

SELECT * WHERE {where clause} GROUP BY profile_id, type ORDER BY created DESC

Thanks for your help.

[stephen84s]

I figured it out last night actually. It was just a matter of a simple GROUP BY like this:

SELECT * WHERE {where clause} GROUP BY profile_id, type ORDER BY created DESC

Thanks for your help.

Hmmm good to see you found a simpler way to work it out,
However I think its best to warn you that if tomorrow you need to replace that "*" with the actual column names you will have a few issues cause the GROUP BY would force you to select the values of only the columns present in the group by clause, and the rest of them would need to be inside a aggregate function.

PS: Had to give above explanation to save face :p , such a simple query and I twisted it beyond recognition.