homepage Welcome to WebmasterWorld Guest from 54.227.141.230
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Need help with SELECT query please!
I have a problem selecting unique rows from a table where two specific colu
tbolt




msg:3959347
 2:46 am on Jul 25, 2009 (gmt 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.

 

HeadBut




msg:3959518
 4:12 pm on Jul 25, 2009 (gmt 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

tbolt




msg:3959523
 5:03 pm on Jul 25, 2009 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved