filter from a table with WHERE clause not constant

Question

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?

score 1 · Accepted Answer · answered Jan 13 '21 at 11:27

1

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.

answered Jan 13 '21 at 11:27

Steve Lovell

2,564
2
13
16

thanks for your solution. unfortunately doesn't seems to work in ms access ("Cannot have aggregate function in WHERE clause"). I see that you define the clm1, but at the end is a min() in the WHERE clause, that is not allowed. But I may may be wrong instead? – Giovanni Jan 13 '21 at 15:09
Sorry for not responding sooner. I must have turned some notifications off and didn't realise you'd replied. As you've accepted my answer as solution, I guess you've worked out what to do. For anyone who needs to know: The code I provided works if put into the SQL editor in Access, but it's fiddly to enter into the visual query editor. You have to separate out two instances of your Clm1, one with the where clause (and not displayed in the output), and the other with the aggregate function. – Steve Lovell Jan 20 '21 at 10:36

T. Peter · Answer 2 · 2021-01-13T07:37:48.227

0

You can use row_number to get the first row:

select clmn0,clmn1 from (
select row_number()over(partition by clmn0 order by clmn1) rn, cte.* 
from [table]
where clmn1 <> 'txt3'
) a
where rn = 1

this query however is for sql-server,and OP didn't mention nor tag the dbms which is in used. SO in different dbms you need to change the row_number syntax to correspond syntax.
here is db<>fiddle for better examine.

RE-EDIT

I'm not sure will this query work in MS-Access but this one didn't use row_number :

select clmn0,clmn1 from (
select a.*, 
      (select top 1 clmn1 from [table] b where a.clmn0 = b.clmn0 order by clmn1) as First_clmn
from [table] a 
) c
where clmn1=First_clmn
and clmn1 <> 'txt3'

I think top syntax do exists in MS-Access, So give it a try.
also here is db<>fiddle.

edited Jan 13 '21 at 07:37

answered Jan 13 '21 at 06:45

T. Peter

887
4
13

Tanks Peter, I'm working with MS-Access, sorry for not mentioning. Can your query be adapted in ms-Jet db contest? – Giovanni Jan 13 '21 at 07:19
this is tricky for me since I know little of MS-Access. But for my best knowledge there is no `row_number` in MS-Access, I'll try to work around for this. – T. Peter Jan 13 '21 at 07:27
@Giovanni edit with query without using `row_number`, not sure will this work or not, but give it a try. – T. Peter Jan 13 '21 at 07:38
@Peter your version for ms access works!. I came up to a different solution that I like to share with you to let me learning: SELECT * FROM Table WHERE clmn1='txt1' UNION SELECT * FROM Table WHERE (clmn0 NOT IN(SELECT clmn0 FROM Table WHERE clmn1='txt1') AND clmn1='txt2'). I'm not sure what's the best in term of performing and why... – Giovanni Jan 13 '21 at 08:49
@Giovanni glad to help, but consider edit your alternative solution in your question. the only issue with your solution is if you have more `txt` to be include, you need to add more subquery, but if the query get what you want, it is a good and valid query! – T. Peter Jan 13 '21 at 08:54

filter from a table with WHERE clause not constant

2 Answers2

RE-EDIT