Access SQL 0/1 variable, 3 criteria: 1. Values in column A identical, 2. Value in column B is NOT the largest, and 3. Value in column C IS the largest

Question

As stated in the title, I need to run some SQL in Access, which gives me a 0/1 variable based on three criterie. Firstly, the new variable must compare across all values in column A, that are the same. Secondly, it has to check, if the value in comlumn B is the largest (in my case, it mustn't be), and thirdly, is has to check if the value of column C is the largest (in my case, it must be).

Normally I would run a simple IIf statement across the columns of one row, but here, I need it to compare across all values in column A, that are the same, and see, if there is a "not largest" value in comlum B with a corresponding "largest value" in column C.

I'we read in a lot of discussions, that you need to create a new query for this purpose, but would it be possible to do it within a query and get a new variable in column D, giving the 0/1 variable?

To make it perfectly clear, my data looks something like the following:

A     B      C
10    4   12-04-2014
10    5   02-17-2015
10    7   08-11-2016
10    8   08-31-2016
10    6   09-05-2016

And there would be houndreds of thousinds of rows, and where A would be different from 10.

So in this case, only the last row would get the value 1 in column D, as it has the latest date, but is not the highest value in column B for the ID value in column A.

Any help is much appreciated!

• Mark

Answer 1

In MySQL, you can do this as:

select t.*,
       iif(x.b <> (select max(tt.b) from t as tt where tt.a = t.a) and
           x.b = (select max(tt.c) from t as tt where tt.a = t.a), 1, 0
          ) as flag
from t;

You can also handle this using aggregation:

select t.*,
       iif(tt.maxb <> t.b and tt.maxc = t.c, 1, 0) as flag
from t inner join
     (select tt.a, max(tt.b) as maxb, max(t.c) as maxc
      from t as tt
      group by tt.a
     ) as tt
     on tt.a = t.a;

Answer 2

SELECT x.a,x.b,x.c,
  CASE
    WHEN x.b<>(SELECT max(xx.b) FROM yourtable xx WHERE xx.a=x.a)
     AND x.c=(SELECT max(xx.c) FROM yourtable xx WHERE xx.a=x.a)
    THEN 1
    ELSE 0
  END AS d
FROM yourtable x

Using OVER (PARTITION BY x.a) would be more efficient, but Access doesn't allow this, AFAIK.