MySql query to MS Access

March 2, 2022

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)

>Solution :

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)