-1

I have a table of names in mysql InnoDB as

Firstname        Lastname
Steve            Ross
John             Doe
J.               Doe
Mike             Ross
Michael          Ross
Roger            Jackson

I want to SELECT rows in which there are duplicates against first letter of Firstname + Lastname.

For the example table, SELECT query should return

John             Doe
J.               Doe
Mike             Ross
Michael          Ross
  • Did you try anything to get this result? – Taryn Apr 10 '15 at 16:56
  • @bluefeet I tried INNER JOIN but no success yet. – Googlebot Apr 10 '15 at 16:58
  • Which DBMS are you using? Postgres? Oracle? – a_horse_with_no_name Apr 10 '15 at 16:59
  • I'd include in your question you attempts. Right now, this is written as a do my work for me type of question. – Taryn Apr 10 '15 at 16:59
  • @a_horse_with_no_name it's mysql. My bad, I included in the question. – Googlebot Apr 10 '15 at 17:02
1

PROPOSED QUERY

SELECT B.firstname,B.lastname FROM
(
    SELECT CONCAT(lastname,LEFT(firstname,1)) nametag,COUNT(1) rcount
    FROM names GROUP BY CONCAT(lastname,LEFT(firstname,1))
    HAVING COUNT(1) > 1
) A
INNER JOIN
(
    SELECT CONCAT(lastname,LEFT(firstname,1)) nametag,
    firstname,lastname FROM names
) B USING (nametag);

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS alldb;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE alldb;
Query OK, 1 row affected (0.00 sec)

mysql> USE alldb
Database changed
mysql> CREATE TABLE names
    -> (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   lastname VARCHAR(25) NOT NULL,
    ->   firstname VARCHAR(25) NOT NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO names (firstname,lastname) VALUES
    -> ('Steve','Ross'),('John','Doe'),('J.','Doe'),
    -> ('Mike','Ross'),('Michael','Ross'),('Roger','Jackson');
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM names;
+----+----------+-----------+
| id | lastname | firstname |
+----+----------+-----------+
|  1 | Ross     | Steve     |
|  2 | Doe      | John      |
|  3 | Doe      | J.        |
|  4 | Ross     | Mike      |
|  5 | Ross     | Michael   |
|  6 | Jackson  | Roger     |
+----+----------+-----------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT B.firstname,B.lastname FROM
    -> (
    ->     SELECT CONCAT(lastname,LEFT(firstname,1)) nametag,COUNT(1) rcount
    ->     FROM names GROUP BY CONCAT(lastname,LEFT(firstname,1))
    ->     HAVING COUNT(1) > 1
    -> ) A
    -> INNER JOIN
    -> (
    ->     SELECT CONCAT(lastname,LEFT(firstname,1)) nametag,
    ->     firstname,lastname FROM names
    -> ) B USING (nametag);
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| John      | Doe      |
| J.        | Doe      |
| Mike      | Ross     |
| Michael   | Ross     |
+-----------+----------+
4 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

  • works like a charm. absolutely marvelous! – Googlebot Apr 10 '15 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.