Welcome to WebmasterWorld Guest from 54.166.33.25

Forum Moderators: open

Need help with SELECT query please!

I have a problem selecting unique rows from a table where two specific colu

   
2:46 am on Jul 25, 2009 (gmt 0)

5+ Year Member



I have a problem selecting unique rows from a table where two specific columns are the same values as a previous 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, 2, 8, 2009-07-12 21:22:47, 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

As you can see, the duplicate row was removed so we're only returning the most up to date notification:

1, 5, 8, 2009-07-21 17:46:34, comment, 1, 12

However, it's not a complete duplicate because of the created date.

Thanks.

4:12 pm on Jul 25, 2009 (gmt 0)

10+ Year Member



Not real clear to me what you are trying to do but look at the "DISTINCT" like "SELECT DISTINCT profile_id , type FROM TABLE xyzyourtable ...

hope that helps

5:03 pm on Jul 25, 2009 (gmt 0)

5+ Year Member



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.

 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month