MySQL Query. How to group one field and keep only unique by other field

Question

I need help with mysql query.

So, I have table like this

---------------------------------------------------------------
ReceivingDateTime   | SenderNumber | TextDecoded | UDH          |
---------------------------------------------------------------
2013-01-31 16:12:19 | +70000001111 | Bla-bla-bla | 050003A70201 |
2013-01-31 16:12:19 | +70000001111 | Bla-bla-bla | 050003A70202 |
2012-01-20 19:24:21 | +70000001111 | Bla-bla-bla |              |
2012-01-18 14:14:19 | +70000002222 | Bla-bla-bla |              |
2012-01-21 13:12:20 | +70000002222 | Bla-bla-bla |              |
2012-01-15 17:12:10 | +70000003333 | Bla-bla-bla | 050003DC0201 |
2012-01-15 17:13:18 | +70000003333 | Bla-bla-bla | 050003DC0202 |

And now my query is

SELECT 
GROUP_CONCAT(TextDecoded SEPARATOR '') TextDecoded,
`ID` 
FROM `inbox`
GROUP BY IF(UDH='',id,SUBSTR(UDH,1,10)) ORDER by `ReceivingDateTime` DESC;

Question

It works almost fine, but I wanna see something like this

-------------------------------------------------------------
ReceivingDateTime   | SenderNumber | TextDecoded            |
-------------------------------------------------------------
2013-01-31 16:12:19 | +70000001111 | Bla-bla-blaBla-bla-bla |
2012-01-21 13:12:20 | +70000002222 | Bla-bla-bla            |
2012-01-15 17:12:10 | +70000003333 | Bla-bla-blaBla-bla-bla |

How I think it should work: Group TextDecoded by UDH, sort by Date, keep only unique SenderNumber which newer than other same SenderNumber. (maybe it's wrong). Sorry for my French.

I don't know will you understand me or not, but I will try to explain. Lets see on iPhone messages, you see only newest message on screen, but when you click on that, there is more. In my case almost same. Even if you will look to Mail.app on Mac OS, all messages grouped by sender. You got it? or need more explanation?:) — Paul K, Mar 05 '14 at 22:07
BhupeshC, I don't know:D lol. I just copied that from another topic and it's work fine for me. Try to do new query, my is for example. — Paul K, Mar 05 '14 at 22:09
I downvoted your question because your query cannot work with your sample data: the `id` column is missing. — AgRizzo, Mar 06 '14 at 00:29

Jeff Horton · Accepted Answer · 2014-03-06T01:14:55.167

EDIT: As noted in the comments whilst MySQL in practice will return the data from the first row it finds in the inner query, it isn't guaranteed to do this. To guarantee this then something like the following would be needed:

SELECT  
    MAX(ReceivingDateTime) AS ReceivingDateTime,
    SenderNumber,
    SUBSTRING_INDEX(GROUP_CONCAT(TextDecoded ORDER BY ReceivingDateTime DESC), ',', 1) AS TextDecoded,
    SUBSTRING_INDEX(GROUP_CONCAT(ID ORDER BY ReceivingDateTime DESC), ',', 1) AS ID
FROM (
    SELECT 
        MAX(ReceivingDateTime) AS ReceivingDateTime,
        SUBSTRING_INDEX(GROUP_CONCAT(SenderNumber ORDER BY ReceivingDateTime DESC), ',', 1) AS SenderNumber,
        GROUP_CONCAT(TextDecoded ORDER BY ReceivingDateTime DESC SEPARATOR '') AS TextDecoded,
        SUBSTRING_INDEX(GROUP_CONCAT(ID ORDER BY ReceivingDateTime DESC), ',', 1) AS ID
    FROM inbox
    GROUP BY IF(UDH='',id,SUBSTR(UDH,1,10)) ORDER BY ReceivingDateTime DESC
) tbl 
GROUP BY SenderNumber

ORIGINAL ANSWER:

You can use a subquery:

SELECT * FROM (
    SELECT 
        `ReceivingDateTime`,
        `SenderNumber`,
        GROUP_CONCAT(`TextDecoded` SEPARATOR '') `TextDecoded`,
        `ID` 
    FROM `inbox`
    GROUP BY IF(`UDH`='',`ID`,SUBSTR(`UDH`,1,10)) ORDER by `ReceivingDateTime` DESC
) tbl
GROUP BY `SenderNumber`;

You may also want to make sure that the inner grouping query is also getting the order right:

SELECT * FROM (
    SELECT 
        MAX(`ReceivingDateTime`) AS `ReceivingDateTime`,
        `SenderNumber`,
        GROUP_CONCAT(`TextDecoded` ORDER BY `ReceivingDateTime` DESC SEPARATOR '') `TextDecoded`,
        `ID` 
    FROM `inbox`
    GROUP BY IF(UDH='',`ID`,SUBSTR(`UDH`,1,10)) ORDER by `ReceivingDateTime` DESC
) `tbl` 
GROUP BY `SenderNumber`;

This assumes that the 10 character start of the UDH has a unique Sender Number, otherwise you will need someting like:

SUBSTRING_INDEX(GROUP_CONCAT(`SenderNumber` ORDER BY `ReceivingDateTime` DESC), ',', 1) AS `SenderNumber`

Which lists the relevant SenderNumbers in the same order, then extracts the first one to ensure we get the data from the row that contains MAX(ReceivingDateTime)

What's the purpose of the outer query's GROUP BY? You aren't using any aggregation functions. — AgRizzo, Mar 06 '14 at 00:25
It makes the SenderNumber unique. When returning data from a column that hasn't had an aggregate function applied, it just returns the value for that column in the first row it finds for each unique value in the GROUP BY clause. Because the inner query is ordered, this will always be the most recent entry for that SenderNumber — Jeff Horton, Mar 06 '14 at 00:27
Why not use DISTINCT? In your case, all other fields will be random when you do not GROUP BY all columns that do not have an aggregation function. — AgRizzo, Mar 06 '14 at 00:31
DISTINCT will return a row for each distinct row across all columns in the result set you can't just specify one column. http://stackoverflow.com/questions/6127338/sql-select-distinct-but-return-all-columns — Jeff Horton, Mar 06 '14 at 00:34
Your implementation is not guaranteed to work. The [manual](http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html) is clear `You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. ` — AgRizzo, Mar 06 '14 at 00:35
That is referring to the ORDER BY in the outer query having no effect. I'm aware of that limitation, hence mentioning the SUBSTRING_INDEX/GROUP_CONCAT trick if the SenderNumber isn't unique over the inner query's GROUP BY. In practice it will always return the data from the first row it finds in the subquery. I do accept that this is not documented functionality and could change in the future, so will posted a correction above. — Jeff Horton, Mar 06 '14 at 01:10

MySQL Query. How to group one field and keep only unique by other field

1 Answers1