CODEWITHSUNDEEP
sqlms-access
Vanessa
Vanessa

Reputation: 5

SQL To MS Access SQL Query

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

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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
                 );

Upvotes: 1

Related Questions