sql - What's the efficient way to select correct data from this table?

Question

Here is the table I have

ID  Key     Value   Lang 
1   key1    text1   en
2   key2    text2   en
3   key3    text3   en
4   key1    chu1    vi
5   key2    chu2    vi

When select the "vi" lang, missing keys will be selected from the "en" lang Basically the result should be

4   key1    chu1    vi
5   key2    chu2    vi
3   key3    text3   en

Is there an efficient way to get the result without complex query like this?

WITH cte_data
AS (
    SELECT ID
        ,Key
        ,Value
        ,Lang
    FROM tblSysText
    WHERE Lang = 'vi'
    )
SELECT ID
    ,Key
    ,Value
    ,Lang
FROM cte_data

UNION

SELECT ID
    ,Key
    ,Value
    ,Lang
FROM tblSysText
WHERE Lang = 'en'
    AND Key NOT IN (
        SELECT Key
        FROM cte_data
        )

Thanks.

---

Update the data set Here is what similar to the real data Basically the table contains the text of several different languages

What I want is to select all the keys and values of a specific language. Then select the missing keys and values from the "en" language

ID  Key     Value   Lang 
1   key1    text1   en
2   key2    text2   en
3   key3    text3   en
4   key1    chu1    vi
5   key2    chu2    vi
6   key4    chu4    vi
7   key4    text4   en
8   key5    text5   en
9   key5    s5      ye
10  key6    s6      ZW

Here is the expected output when select for "vi" language

ID  Key     Value   Lang
4   key1    chu1    vi
5   key2    chu2    vi
6   key4    chu4    vi
3   key3    text3   en
8   key5    text5   en

Answer 1

I would use ROW_NUMBER along with a TOP 1 WITH TIES trick here:

SELECT TOP 1 WITH TIES ID, [Key], Value, Lang
FROM tblSysText
ORDER BY ROW_NUMBER() OVER (PARTITION BY [Key] ORDER BY Lang DESC);

Demo

The logic here is to retain, for each key, the record with the "highest" language value. For those keys having both en and vi records, it would choose the latter record, otherwise it would default to choosing the former.