CODEWITHSUNDEEP
mysqlsqlcasesql-order-by
tomato_paste
tomato_paste

Reputation: 23

MySQL ORDER BY CASE but keep duplicates

I have a database with the format of

| Field      | Type             | Null | Key | Default | Extra          |
| word       | varchar(60)      | NO   |     | NULL    |                |
| english_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |


| word | english_id |
| the  |          1 |
| of   |          2 |
| and  |          3 |
| a    |          4 |
| to   |          5 |
| in   |          6 |
| ant  |          7 |
| be   |          8 |
| that |          9 |
| was  |         10 |

I want to be able to order by the order of the input, but also to keep duplicates. So if they enter "a cat and a dog" I get the proper order with the SQL query below. The problem is that since I use a twice the second a doesn't appear. How do I keep duplicate values in order in the output? Here is the current query.

select english_id, word from english where word in ("Here", "are", "are", "dogs")
order by CASE word
  WHEN "here" then 1
  WHEN "are" then 2
  WHEN "are" then 3
  WHEN "dogs" then 4
END;

Here is its output

| english_id | word |
|        131 | here |
|         26 | are  |
|       1679 | dogs |

I want it to look like this

| english_id | word |
|        131 | here |
|         26 | are  |
|         26 | are  |
|       1679 | dogs |

Upvotes: 2

Views: 492

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26343

To get are to show up twice, you need to join to a "table" where are shows up twice. You can get the table like this:

SELECT 'Here'
UNION ALL SELECT 'are'
UNION ALL SELECT 'are'
UNION ALL SELECT 'dogs'

Just join your english table to that in a query, and it should work fine:

SELECT english_id, english.word
FROM english
JOIN (
    SELECT 'Here' AS word
    UNION ALL SELECT 'are'
    UNION ALL SELECT 'are'
    UNION ALL SELECT 'dogs') WordsToSearch ON english.word = WordsToSearch.word
ORDER BY CASE word
  WHEN "here" then 1
  WHEN "are" then 2
  WHEN "are" then 3
  WHEN "dogs" then 4
  END;

You can also specify the order information in your word list to simplify the query a bit:

SELECT english_id, english.word
FROM english
JOIN (
    SELECT 'Here' AS word, 1 AS word_order
    UNION ALL SELECT 'are', 2
    UNION ALL SELECT 'are', 3
    UNION ALL SELECT 'dogs', 4) WordsToSearch ON english.word = WordsToSearch.word
ORDER BY WordsToSearch.word_order;

Addendum

OP ran into trouble with the query as originally posted because the column word in the first line of the query wasn't aliased, and very kindly posted the updated query in the comments. The queries above have been updated for this fix.

Upvotes: 3

Related Questions