Search code examples
mysqlwhere-clauselimit

MySQL select where first three letters are different

Is it possible to do a select statement where it only returns results where the first three letters of the words that you are selecting are different? If, for example, select russian from info where english = 'defense'; produces these results обороне,оборонного,оборонное

Then can i say something like select russian from info where english = 'defense' and where first three letters are not 'обо';

This, for example, would result only in. `обороне'

An example of what the table looks like is below.

id,  english,  russian
1    defense   обороне
2    defense   оборонного
3    defense   оборонное
4    defense   другой

I would want one of the results that begins with обо, and I would want другой as well

Solution

  • You can group by english and the first 3 chars of russian and return the min value of russian:

    SELECT english, MIN(russian) russian
FROM info
GROUP BY english, LEFT(russian, 3)

    See the demo.
    Results:

    english russian
    defense обороне
    defense другой