Mysql: how to find the different other row values when one of the rows starts with the same strings

Question

I have a mysql table called entries. The rows are : member, id, title and added

With PhpMyAdmin, among the latest 100 lines (sorted by: added, desc), I need to:

• only work on a cases when the title starts with the same 20 strings (the end of the string varies, not the beginning)
• and when the title contains the same beginning, then it's time to produce a list containing (1) the full title, (2) the id and (3) the member names that had used this title.
• That list will be checked with the naked eye after this, so there are no strict rules as to how the list must look, as long as it works it's good. Eventually, that will be more like the latest 1 000 entries, so the naked eye needs this pre-filtering.

The best I can do myself (sorry!) is to output a generic list of EVERY entry, and manually search, with my eyes, the cases when the title starts with the same characters:

SELECT title, member FROM (
SELECT * FROM entries
ORDER BY added
DESC
LIMIT 0, 100
) AS olitest
ORDER BY title

From a past question on stackoverflow that I wrote too poorly, two incomplete suggestions were made, they may be a start

That one won't give the member and id and isn't limited to latest 100 lines

select left(e.title, 20), group_concat(title) as titles
from entries e
group by left(e.title, 20)
having count(*) > 1;

That one displays the total number of lines for a title, essentially.

select left(e.title, 20), count(*), max(title) 
from entries e
group by left(e.title, 20)
having count(*) > 1;

More is beyond my skill, alas. Do you see how that can be done ?

Answer 1

I am posting this as answer but I don't think it will get you to your final result. I am doing it for the formatting and readability since comments do not provide formatting.

I have been working on a query similar to your needs with no luck but this might get you closer.

CTE is a Common Table Expression. It builds a temp table to work on

WITH CTE (left(title, 20), DuplicateCount)
 AS
 (
 SELECT left(title, 20),
 ROW_NUMBER() OVER(PARTITION BY left(title, 20) ORDER BY ID) AS DuplicateCount
 FROM entries
 )
 SELECT *
 FROM CTE
 WHERE DuplicateCount > 1

This should give you an output with the DuplicateCount showing next to the titles with matching names. When ever DuplicateCount is greater than 1, you will need to do your work.

If none of that works try this.....

 SELECT left(title, 20),
 ROW_NUMBER() OVER(PARTITION BY left(title, 20) ORDER BY ID) AS DuplicateCount
 FROM entries