3

I want to get unique message (datatype: TEXT). I am using this simple query to achieve this:

SELECT DISTINCT message FROM `online_communication`

Results I am getting are:

hello.. what's up?
second message..
third message.....
third message.....

Why am I getting third message..... 2 times? I also tried with:

SELECT DISTINCT BINARY message FROM `online_communication`

But, getting same number of rows in BLOB

PS: No luck with GROUP BY as well.

SQLFiddle: http://sqlfiddle.com/#!9/304e4/1

RNK
  • 5,582
  • 11
  • 65
  • 133

3 Answers3

2

This is behaving as expected because your values are different. From your SQL fiddle: 

INSERT INTO `online_communication` (`id`, `company_id`, `country_id`, `message`, `date_added`, `time_interval`) VALUES
(53, 1, 210, 'third message.....\r\nand here..\r\n', '2015-05-01 11:05:31', '1'),
(61, 1, 103, 'third message.....\r\nand here.. \r\n', '2015-05-01 11:06:38', '1');

Notice that the first insert does not have a space after the two periods, but the second one does. They look the same in the results, because the difference just represents white space.

EDIT

By definition, these are unique messages, so your query is fine. However, if you want to treat them as the same, you need to find a business rule for what defines them as the same.

For example, you could just remove all spaces from the string, and compare them that way:

SELECT DISTINCT REPLACE(message, ' ', '') FROM `online_communication`

Here is a working Fiddle example.

Here are some helpful string functions. Notice that you cannot use TRIM() here because that function only removes whitespace from the beginning or end of a string. In this example, it is in the middle, so REPLACE is more useful.

AdamMc331
  • 16,492
  • 10
  • 71
  • 133
  • Ya. That's the data inserted by user. But, how can I avoid that extra space while getting data? What should I do to get unique messages? – RNK May 01 '15 at 19:16
  • @RahulTripathi: Itried with: `SELECT DISTINCT trim(message) FROM online_communication` still it's giving me double messages – RNK May 01 '15 at 19:20
  • @RahulTripathi TRIM will not work here, because the white space is inside the string and not on either end of it. `REPLACE()` is the string function you are looking for. – AdamMc331 May 01 '15 at 19:20
  • @RonakPatel:- Yes instead use `REPLACE(message, ' ', '')` – Rahul Tripathi May 01 '15 at 19:21
  • And I can not display that message without any spaces to users – RNK May 01 '15 at 19:21
  • @RonakPatel I have updated my answer to get you the results you need. – AdamMc331 May 01 '15 at 19:23
  • @RonakPatel you do not need GROUP BY. It will do the same thing as `DISTINCT` in this particular example. Since the values are not the same, they cannot be grouped, unless you group using replace syntax as well. – AdamMc331 May 01 '15 at 19:25
  • 1
    But, As I mentioned, I can not display messages without spaces to users.. If I will use this then it'll solve all problems.. `SELECT message FROM online_communication GROUP BY REPLACE(message, ' ', '')` It'll give me results with spaces and unique. – RNK May 01 '15 at 19:27
  • How about SELECT DISTINCT REPLACE(message, ' \r\n', '\r\n') FROM `online_communication` – Rick May 01 '15 at 19:28
  • @RonakPatel sure, but what that will do is arbitrarily select one of the two messages. Try selecting ID instead of message with that code and you will see that only one of the two messages is returned. – AdamMc331 May 01 '15 at 19:28
1

One of your line has an extra space

(53, 1, 210, 'third message.....\r\nand here..\r\n', '2015-05-01 11:05:31', '1'),
(61, 1, 103, 'third message.....\r\nand here.. \r\n', '2015-05-01 11:06:38', '1');

Remove that and you will get the expected result.

SQL FIDDLE DEMO

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • Ya. That's the data inserted by user. But, how can I avoid that extra space while getting data? What should I do to get unique messages? – RNK May 01 '15 at 19:17
0

Are there any spaces at the end of the one of the instances of "third message....."? According to the documentation, trailing spaces aren't removed from TEXT types. You could check with a query like this:

SELECT DISTINCT concat('[', message,  ']') FROM `online_communication`

You'll be looking for something like this in the results:

[hello.. what's up?]
[second message..]
[third message.....]
[third message.....   ]
Rick
  • 4,575
  • 1
  • 26
  • 20
  • I tried that. I can not find any difference. Can you please check in on sqlfiddle. – RNK May 01 '15 at 19:14
  • You got a bunch of answers all at once - I see others have looked at the data and pointed out the specific extra space. – Rick May 01 '15 at 19:25