CODEWITHSUNDEEP
mysqlsqldatabase
Jared Miller
Jared Miller

Reputation: 893

Joining to a second table where a field has the highest number of occurences of a character

I need to join to a particular table, but I only want to get the row from the second table that has the highest number of occurrences of a particular character.

For example Two Tables One to many relationship from a -> b

b has a foreign key and a string. I only want the row from b that contains the highest number of '/' characters.

select a.*, b.string
from a join b on a.pk = b.fk

I can get the highest number by doing something like.

MAX(LENGTH(b.string) - LENGTH(REPLACE(b.string, '/', '')))
group by b.fk

But thats not something I can join on.

I tried doing an order by with a limit 1 with a subquery but doing limits is not supported in my version of MySQL.

Upvotes: 2

Views: 25

Answers (2)

Strawberry
Strawberry

Reputation: 33945

  DROP TABLE IF EXISTS my_table;

  CREATE TABLE my_table
  (id INT NULL AUTO_INCREMENT PRIMARY KEY
  ,string_id INT NOT NULL
  ,string VARCHAR(12) NOT NULL
  ,UNIQUE (string_id,string)
  );

  INSERT INTO my_table (string_id,string) VALUES
  (1,'///'),
  (1,'////'),
  (1,'/////'),
  (1,'//'),
  (1,'/'),
  (2,'/'),
  (2,'//'),
  (3,'///'),
  (3,'//'),
  (4,'///'),
  (4,'/');

  SELECT * FROM my_table;
  +----+-----------+--------+
  | id | string_id | string |
  +----+-----------+--------+
  |  5 |         1 | /      |
  |  4 |         1 | //     |
  |  1 |         1 | ///    |
  |  2 |         1 | ////   |
  |  3 |         1 | /////  |
  |  6 |         2 | /      |
  |  7 |         2 | //     |
  |  9 |         3 | //     |
  |  8 |         3 | ///    |
  | 11 |         4 | /      |
  | 10 |         4 | ///    |
  +----+-----------+--------+

  SELECT x.*
    FROM my_table x
    JOIN ( SELECT string_id,MAX(LENGTH(string) - LENGTH(REPLACE(string, '/', ''))) max_string FROM my_table GROUP BY string_id) y
      ON y.string_id = x.string_id
     AND y.max_string = LENGTH(x.string) - LENGTH(REPLACE(x.string, '/', ''));
  +----+-----------+--------+
  | id | string_id | string |
  +----+-----------+--------+
  |  3 |         1 | /////  |
  |  7 |         2 | //     |
  |  8 |         3 | ///    |
  | 10 |         4 | ///    |
  +----+-----------+--------+

Upvotes: 1

user2881767
user2881767

Reputation: 300

You could try the query you have written with the HAVING clause where the HAVING clause can specify the expression you require as

SELECT... FROM... GROUP BY... HAVING MAX(LENGTH(b.string) - LENGTH(REPLACE(b.string,'/','')));

Upvotes: 0

Related Questions