MySQL - changing the query for difference by only 1 column

Question

I have the following query that works, except that I would like it to behave differently. Now it searches for all duplicate lines in the url and returns it in the order of the number of duplicate URLs. I use GROUP_ CONCAT to separate all the different screen_names.

However, there may be multiple lines with the same URL and the same screen_name. How can I do this so that it only returns strings where screen_name matters.

 SELECT url, title, GROUP_CONCAT( screen_name ) , COUNT( t_uid ) FROM `twl_links` WHERE twl_uid =3 AND timestamp >= NOW( ) - INTERVAL 24 HOUR GROUP BY ( url ) ORDER BY COUNT( t_uid ) DESC 

Thank you very much,

Ice

Answer 1

It is not clear what you want. Given this data:

 t_uid url title screen_name 1 http://google.com/ Google bob 2 http://google.com/ Google Search bob 3 http://google.com/ Google tom 

what results do you expect? If you want to:

 http://www.google.com '???' 'bob,tom' 2 

then you can make a SELECT DISTINCT subquery to remove duplicates (which you put instead of FROM twl_links ).

If you want to

 http://www.google.com '???' 'bob,tom' 3 

then you can do GROUP_CONCAT(DISTINCT screen_name) to get this.

Note that in all cases, the returned header is ambiguous (if there is no N: 1 mapping between the URL and the header, that is, if each URL has only one separate header)

FYI, it looks like you have redundant and inaccessible data due to lack of normalization.

Answer 2

You can restrict it using this subquery:

 SELECT t1.url, GROUP_CONCAT( t1.screen_name ) , COUNT( * ) FROM ( SELECT DISTINCT url, screen_name FROM mytable ) AS t1 GROUP BY t1.url 

This does not give you a name, but since the name is not in GROUP BY, it was not entirely clear to me that this field would be returned anyway. If you need all the headers for these URLs, you can join the above query with your table.

Answer 3

Have you tried using the keyword?

http://www.tech-recipes.com/rx/1481/ignore-duplicate-entries-in-mysql-select-using-distinct-keyword/

EDIT:

Here is another article showing where various can be used with group_concat:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html