mysql组并删除重复项

Question

I have got this mySQL table:

 id | url | origin | categroy | date 

 1 | url.com/1 | US | news | 2015-10-01 12:39:19
 2 | url.com/1 |    |      | 2015-10-01 13:59:34
 3 | url.com/2 | CN | news | 2015-09-01 12:45:26
 4 | url.com/3 |    |      | 2015-08-12 09:10:10
 5 | url.com/3 | US | news | 2015-09-14 04:56:36
 6 | url.com/4 | US |      | 2015-09-09 12:12:09

is there a way to group the entries by the url and remove the duplicates in one mySQL call without creating a new table?

desired table:

 1 | url.com/1 | US | news | 2015-10-01 12:39:19
 3 | url.com/2 | CN | news | 2015-09-01 12:45:26
 5 | url.com/3 | US | news | 2015-09-14 04:56:36
 6 | url.com/4 | US |      | 2015-09-09 12:12:09

I am a beginner and I tried to solve this with a php script which failed. I guess there is a fairly easy SQL-answer to that, but I couldn't find an easy answer on Stackoverflow.

Thank you very much!

Answer 1

Try this query

SELECT id, url, 
SUBSTRING_INDEX(GROUP_CONCAT(origin ORDER BY date DESC),',',1) as origin_value,  
 SUBSTRING_INDEX(GROUP_CONCAT(category ORDER BY date DESC),',',1) as category_value, 
 SUBSTRING_INDEX(GROUP_CONCAT(date ORDER BY date DESC),',',1) as date_value FROM tablename 
GROUP BY url
ORDER BY url,date DESC