1

I want to select from the following table all the rows which have similar values in the fname column as the first in their order. IOW from this table I want to retrieve rows with ids 2,5 and 7 (because " anna" comes after "anna", and "michaela" and "michaal" come after "michael").

+----+------------+----------+
| id | fname      | lname    |
+----+------------+----------+
|  1 | anna       | milski   |
|  2 |  anna      | nguyen   |
|  3 | michael    | michaels |
|  4 | james      | bond     |
|  5 | michaela   | king     |
|  6 | bruce      | smart    |
|  7 | michaal    | hardy    |
+----+------------+----------+

What I have so far is this:

select *, count(fname) cnt 
from users group by soundex(fname) 
having count(soundex(fname)) > 1;

but since I'm grouping it the result is

+----+----------+----------+-----+
| id | fname    | lname    | cnt |
+----+----------+----------+-----+
|  1 | anna     | milski   |   2 |
|  3 | michael  | michaels |   3 |
+----+----------+----------+-----+

What I want retrieved is this:

+----+----------+----------+-----+
| id | fname    | lname    | cnt |
+----+----------+----------+-----+
|  2 |  anna    | nyugen   |   2 |
|  5 | michaela | king     |   3 |
|  7 | michaal  | hardy    |   3 |
+----+----------+----------+-----+

What should I change about the query? I tried removing "group by" but it changes the results (I could be wrong, haven't tested it extensively).

Gal
  • 23,122
  • 32
  • 97
  • 118

2 Answers2

2

I've re-read your initial question and I've came up with the following solution:

SELECT *
FROM   users
WHERE  id IN
       (SELECT id
       FROM    users t4
               INNER JOIN
                       (SELECT  soundex(fname) AS snd,
                                COUNT(*)       AS cnt
                       FROM     users          AS t5
                       GROUP BY snd
                       HAVING   cnt > 1
                       )
                       AS t6
               ON      soundex(t4.fname)=snd
       )
AND    id NOT IN
       (SELECT  MIN(t2.id) AS wanted
       FROM     users t2
                INNER JOIN
                         (SELECT  soundex(fname) AS snd,
                                  COUNT(*)       AS cnt
                         FROM     users          AS t1
                         GROUP BY snd
                         HAVING   cnt > 1
                         )
                         AS t3
                ON       soundex(t2.fname)=snd
       GROUP BY snd
       );

It's a bit over-complicated, but it works and delivers exactly what you asked for :)

GreyCat
  • 16,622
  • 18
  • 74
  • 112
  • woohoo! that's exactly it. you rock! thank you so much. I really appreciate it, it will definitely save my a$$ at work :) – Gal Feb 09 '11 at 11:57
  • btw I've tried doing it myself, but it hasn't worked out. I tried completely removing the "AND id NOT IN" clause and instead providing the query with "id>min(id)" somewhere so it will only return the second row onward in order (which is what I want and seems a bit less convoluted). Do you have a clue how to go about that? – Gal Feb 09 '11 at 12:28
  • You can't go around GROUP BY returning only single ("aggregated") row if you're using GROUP BY at all. By using `id > MIN(id)` you'll still get only one row, but this time, second one, not the first one. – GreyCat Feb 09 '11 at 15:40
0

You seem to get what you're asking for - SOUNDEX(fname) would make Soundex hashes only from first name, not whole string. A few of options you can investigate:

SELECT *, COUNT(SOUNDEX(CONCAT(fname, lname))) AS cnt
GROUP BY SOUNDEX(CONCAT(fname, lname))
HAVING cnt > 1;

or 

SELECT *, COUNT(SOUNDEX(fname)) AS cnt1, COUNT(SOUNDEX(lname)) AS cnt2
GROUP BY SOUNDEX(fname), SOUNDEX(lname)
HAVING cnt1 > 1 OR cnt2 > 1

It depends on what do you want to achieve: count of similar first name, last names or some synth hash of both.

GreyCat
  • 16,622
  • 18
  • 74
  • 112