Member Avatar for adam.adamski.96155

Hello :D
I am using the twitter API to perform a search for a certain author, and storing the results in a database so that I can perfrom an action on each entry. However, there are many duplicate results that have small differences at the beginning of the string or at the end that I do not want to respond to. I have managed to select all the distinct entries based on a substring:
SELECT DISTINCT (SUBSTRING(msg_text FROM 10 FOR 75)) AS res FROM goethe_search;
...which gives me the dinstinct substrings but not the other fields that corelate to the distinct results. I tried this:
SELECT * FROM (SELECT DISTINCT (SUBSTRING(msg_text FROM 10 FOR 75)) FROM goethe_search) AS res;,
and this:
SELECT DISTINCT (SUBSTRING(msg_text FROM 10 FOR 75)) AS res, id FROM goethe_search GROUP BY id;,
but they both give me all the results and I lose the distinct aspect. Can anyone point me in the right direction?
Thanks in advance :D

  • 1 Contributor
  • 1 Reply
  • 4K Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by adam.adamski.96155
Member Avatar for adam.adamski.96155

I found an answer using the GROUP BY clause:
SELECT * FROM goethe_search GROUP BY (SUBSTRING(msg_text FROM 10 FOR 75));
Easy when you know how :D

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.