SQL To MS Access SQL Query

Question

Hello SQL/MS Access community! I have a problem! I have a query in SQL that gets the top record for each group and it works great! However, I need this to be a query in Microsoft Access. Is there anyone out there that can help me with translating it? I do not know coding at all (I can pick it apart to understand it, but I can't write it unfortunately). Any help is much appreciated!

SELECT * FROM Table1; WITH summary AS ( SELECT p.PK_ID, p.Field1, p.Field2, ROW_NUMBER() OVER (PARTITION BY p.PK_ID ORDER BY p.Field1 DESC) AS rk FROM Table1 p) SELECT s.* FROM summary s WHERE s.rk=1

Gordon Linoff · Accepted Answer · 2016-02-04 00:43:53Z

1

You are trying to get the first record (based on Field1) for each pk_id. Something like this may work for you:

select p.*
from table1 as p
where p.field1 = (select max(p2.field1)
                  from table1 as p2
                  where p2.pk_id = p.pk_id
                 );

answered Feb 4, 2016 at 0:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

SQL To MS Access SQL Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related