CODEWITHSUNDEEP
sqlsql-server
comXyz
comXyz

Reputation: 9

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

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

Upvotes: 0

Views: 76

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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

You can also express this using not exists:

select st.*
from tblSysText st
where st.lang = 'vi' or
      (st.lang = 'en' and
       not exists (select 1
                   from tblSysText st2
                   where st2.key = st.key and
                         st2.lang = 'vi'
                  )
      );

With an index on tblSysText(key, lang), I would expect this to have somewhat better performance than solutions using window functions.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

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);

screen capture from demo link below

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.

Upvotes: 2

Popeye
Popeye

Reputation: 35910

You can use analytical function row_number as follows:

select * from
(SELECT ID
    ,Key
    ,Value
    ,Lang
    ,row_number() over (partition by key order by Lang desc) as rn
FROM tblSysText
WHERE Lang in ('en','vi')) t
where rn = 1

Upvotes: 1

Related Questions