CODEWITHSUNDEEP
mysqlselect
user4920811
user4920811

Reputation:

How to select adjacent columns in MySQL

My table: vocabulary

    id          word
--------------------------
    1           hello
    2           hello
    3           how
    4           how
    5           how
    6           are
    7           hello
    8           hello
    9           are
    10          are
    11          are
    12          are
    13          hello

I want to select id from vocabulary where id=$id and {all rows that are both the same word and adjacent}

Note: I want both of them: [id=$id] and [all rows that are both the same word and adjacent]

In fact, I need to a SELECT query to do something like this: (three examples)

  1. $id=1, result: 1,2 // [1 for $id=1] - [2 for the word of 1 and 2 are the same and adjacent]
  2. $id=6, result: 6 // [6 for $id=6]
  3. $id=10,result: 9,10,11,12 // [10 for$id=10]-[9,11,12 for the word of 10 is the same with 9,11,12]

Upvotes: 3

Views: 350

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Here's a basic pattern that you can adapt to your purpose...

DROP TABLE IF EXISTS my_table;

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

INSERT INTO my_table VALUES
(1 ,'hello'),
(2 ,'hello'),
(3 ,'how'),
(4 ,'how'),
(5 ,'how'),
(6 ,'are'),
(7 ,'hello'),
(8 ,'hello'),
(9 ,'are'),
(10,'are'),
(11,'are'),
(12,'are'),
(13,'hello');

SELECT a.id start
     , MIN(c.id) end 
  FROM my_table a
  LEFT 
  JOIN my_table b 
    ON b.id = a.id - 1
   AND b.word = a.word
  LEFT 
  JOIN my_table c 
    ON c.id >= a.id
   AND c.word = a.word
  LEFT 
  JOIN my_table d 
    ON d.id = c.id + 1
   AND d.word = a.word
 WHERE b.id IS NULL 
   AND c.id IS NOT NULL
   AND d.id IS NULL
 GROUP 
    BY a.id; 

+-------+------+
| start | end  |
+-------+------+
|     1 |    2 |
|     3 |    5 |
|     6 |    6 |
|     7 |    8 |
|     9 |   12 |
|    13 |   13 |
+-------+------+

As McAdam331 suggests, one way of extending this idea is as follows: 

SELECT *  
  FROM vocabulary 
  JOIN tmpTable 
 WHERE id BETWEEN tmpTable.start AND tmpTable.end 
  AND tmpTable.start = $id;

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

This is a solution using variables:

SELECT id, word
FROM (
  SELECT id,       
         @rnk:= CASE WHEN @word = word THEN @rnk 
                   ELSE @rnk + 1
              END AS rnk,
         @word:= word AS word
  FROM vocabulary, (SELECT @rnk:=0) as vars    
  ORDER BY id ) s
WHERE s.rnk = (
    SELECT rnk
    FROM (
      SELECT id,       
             @r:= CASE WHEN @w = word THEN @r 
                       ELSE @r + 1
                  END AS rnk,
             @w:= word AS word
      FROM vocabulary, (SELECT @r:=0) as vars    
      ORDER BY id ) t
    WHERE id = 10) -- 10 is equal to $id

SQL Fiddle Demo

The same query is repeated two times, due to lack of CTEs in MySQL. @rnk and @r variables are used to identify islands of continuous word values within vocabulary table.

The second query picks the island value (e.g. @r = 5 for id = 10) and the first one uses this value to select all records that belong to the same island.

Upvotes: 3

Related Questions