Question 301705 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL GROUP BY while selecting the latest record + extra conditions

I am trying to figure out what would be the most efficient way of achieving the following:

I have two tables:

word_translations(id, word_id, language_id)

and

translations(id, text, word_translation_id, created_at, updated_at, user_id)

So there is a one-to-many relationship between word_translations and translations table. If a translation is created with user_id not null, the user has provided his own translation and therefore overrides the default ones (there can be at most one (user) translation override per word_translation per user, but multiple default translations per word_translation).

I am trying to write SQL query, that returns a list of translation ids with following conditions:

  • There is only a single translation id for each word_translation_id
  • The translation id points to the latest translation of a specific word_translation (max(id)), unless there is other translation with the same word_translation_id that has user_id not null. In this case, it should point to translation id where the user_id is not null.

I have written the following query to do so, but I am not sure about it’s performance:

SELECT IF(t2.id IS NULL, t1.id, t2.id) as id, t1.word_translation_id
FROM (
    SELECT MAX(t.id) as id, t.word_translation_id
    FROM translations t
    WHERE t.user_id IS NULL
    GROUP BY t.word_translation_id) as t1
LEFT JOIN (
    SELECT t.id, t.word_translation_id
    FROM translations t
    WHERE t.user_id IS NOT NULL) t2 
ON t1.word_translation_id = t2.word_translation_id;

Is there a more efficient way to do this? (Also, do you see any issues with the query above?)

Tags:

2

Answers


  1. 0

    I think you might be able to simplify this using an anti-join:

    SELECT MAX(t.id) AS id, t.word_translation_id
FROM translations t
LEFT JOIN translations t2 ON
    t.user_id IS NULL
    AND t.word_translation_id = t2.word_translation_id
    AND NOT (t.id = t2.id)
    AND (t2.id > t.id OR t2.user_id IS NOT NULL) 
WHERE t2.id IS NULL
GROUP BY t.word_translation_id

    For each translation, look for another translation for the same word that has either a later id, or a user_id that is not null (the JOIN conditions). Then exclude any rows where that is true (the WHERE clause).

    Login or Signup to reply.
  2. 0

    Use two queries that you combine with UNION.

    Query 1 returns the newest default translation for words that don’t have any user translations.

    SELECT MAX(t1.id), t1.word_translation_id
FROM translations AS t1
LEFT JOIN translations AS t2 ON t1.word_translation_id = t2.word_translation_id AND t2.user_id IS NOT NULL
WHERE t2.id IS NULL
GROUP BY t1.word_translation_id

    Query 2 returns user translations.

    SELECT DISTINCT id, word_translation_id
FROM translations
WHERE user_id IS NOT NULL

    Final query:

    SELECT MAX(t1.id) AS id, t1.word_translation_id
FROM translations AS t1
LEFT JOIN translations AS t2 ON t1.word_translation_id = t2.word_translation_id AND t2.user_id IS NOT NULL
WHERE t2.id IS NULL
GROUP BY t1.word_translation_id

UNION ALL

SELECT DISTINCT id, word_translation_id
FROM translations
WHERE user_id IS NOT NULL
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search