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!
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