Welcome to WebmasterWorld Guest from 54.167.110.211

Forum Moderators: open

Message Too Old, No Replies

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)

New User

5+ Year Member

joined:July 25, 2009
posts: 2
votes: 0


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 July 25, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:Sept 2, 2004
posts:187
votes: 0


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 July 25, 2009 (gmt 0)

New User

5+ Year Member

joined:July 25, 2009
posts:2
votes: 0


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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members