MySql query to MS Access

Question

So I'm trying (actually made it work in MySql) to grab 3 columns from the source table, remove its duplicates just based on 2 of those 3 columns (that's why I need the partition and row_number), and once duplicates are dropped, I'm checking into the target table if those same 2 values are not already existing.

But now I have to get through MS Access and found out partition by and row_number() are not supported.

Any approach will be appreciated

Here's the query:

INSERT INTO TARGET_TABLE (COL1, CV1, CV2)
SELECT st.DontCareValue, st.CV1, st.CV2
FROM(
select DontCareValue, CV1, CV2,
row_number() over (partition by CV1, CV2 order by DontCareValue) as rn
From sourceTable
where length(CV1) >= 2) st
where st.rn = 1
and not exists(select 1 from TARGET_TABLE tt
where tt.CV1= st.CV1
and tt.CV2 = st.CV2)

You just seem to take the min value of DontCareValue per CV1 and CV2 combinations (order by without ASC/DESC defaults to ASC, so the first item is the minimum. So, use group by on CV1, CV2 and get the min value. — Shadow
– Shadow, Commented Mar 2, 2022 at 18:11
Thanks! It actually worked as you described. Nice workaround! :) — Alejandro Peralta
– Alejandro Peralta, Commented Mar 2, 2022 at 18:58

xQbert · Accepted Answer · 2022-03-02 18:50:46Z

1

This is what Shadow is saying:

Keep in mind prior to the new useful analytic features, the same problems existed in the past. You just need to find the appropriate design pattern from then. You're getting RN=1 which would be the smallest value of DontCare for each CV1, CV2 group. so Just use a min in your su

INSERT INTO TARGET_TABLE (COL1, CV1, CV2)

SELECT st.MinDontCareValue, st.CV1, st.CV2
FROM (SELECT Min(DontCareValue) MinDontCareValue, CV1, CV2
      FROM sourceTable
      WHERE length(CV1) >= 2
      GROUP BY CV1, CV2) st

WHERE not exists (SELECT 1 
                  FROM TARGET_TABLE tt
                  WHERE tt.CV1= st.CV1
                    AND tt.CV2 = st.CV2)

answered Mar 2, 2022 at 18:50

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alejandro Peralta Over a year ago

In fact, I followed up Shadow suggestion, whic came pretty close to this one (which is more simplified) thanks a lot!

Collectives™ on Stack Overflow

MySql query to MS Access

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related