[SOLVED] filter from a table with WHERE clause not constant

filter from a table with WHERE clause not constant

In TableA clmn1 are accounts, clmn2 can take 3 value : txt1, txt2 or txt3. For each account you can have more than one value in clmn2. I need to filter accounts to take only txt1 or txt2, where txt1 is preferred over txt2 if there are both. Txt3 is excluded

      TableA
  clmn0 clmn1
    1      txt1
    1      txt2
    1      txt3
    2      txt2
    2      txt3
    3      txt1
    3      txt3
    4      txt3
    5      txt2
    6      txt1
    6      txt2
   …      ….

Table filtered:

   clmn0 clmn1
     1      txt1
     2      txt2
     3      txt1
     5      txt2
     6      txt1
    …      ….

My attempt is:

SELECT * 
FROM TableA 
Where 
(clmn1='txt1' OR clmn1='txt2') AND clmn1='txt1'
OR
(clmn1<>'txt1' OR clmn1<>'txt2') AND clmn1='txt2'

but I get

      clmn0 clmn1
        1   txt1
        1   txt2
        2   txt2
        3   txt1
        5   txt2
        6   txt1
        6   txt2

I came up to a different solution of @T.Peter, not sure what performing better:

SELECT * FROM Table WHERE clmn1='txt1' UNION SELECT * FROM Table WHERE
(clmn0 NOT IN(SELECT clmn0 FROM Table WHERE clmn1='txt1') AND
clmn1='txt2')

thanks @hansUp. I apologize, Steave is absolutely right in this example. However as Steave said the real situation is more complex. the strings txt1, tx2, txt3... are only example and cannot be ordered. I could add a clmn2 though, where at txt1 in clmn1 match clmn2=1, txt2 clmn2=2, txt3 clmn3=3 an so on with a sort of ranking, so the MIN() function works...Wondering how the aggregation in GROP BY should be?

Solution

I may be missing something, but isn't it simpler to do it with a simple MIN?

SELECT clm0, min(clm1) as clm1
FROM [table]
WHERE clm1 <> 'txt3'
GROUP BY clm0

Perhaps the real world situation makes it more complicated than it seems with this sample data.