Transforming Access Query to SQL: Handling Last Date and Maximum Value

Data and Databases
1

Hey everyone,

I’m trying to switch an Access query over to SQL. The tricky part is dealing with the Last and Max functions, especially when using them with GROUP BY and HAVING clauses. Here’s what I’m working with:

SELECT customer.id, customer.agent, customer.first_name, customer.last_name,
       customer.region, customer.town, customer.phone,
       MAX(interaction.follow_up) AS latest_follow_up,
       customer.account_status,
       LAST(interaction.contact_date) AS recent_contact,
       LAST(interaction.connection_type) AS recent_connection,
       customer.clinic,
       customer.insurance_details,
       -- More fields omitted for brevity
FROM customer
JOIN interaction ON customer.id = interaction.customer_id
GROUP BY customer.id, customer.agent, customer.first_name, -- other fields
HAVING customer.agent LIKE 'REP%'
       AND MAX(interaction.follow_up) <= GETDATE()
       AND customer.account_status NOT IN (1, 4, 5)
ORDER BY MAX(interaction.follow_up) DESC,
         customer.account_status,
         LAST(interaction.connection_type)

Any ideas on how to make this work in standard SQL? Thanks in advance!

2

yo, thats tricky! use ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY contact_date DESC) in a subquery to pick the latest row for each customer and join it back to ur main query. hope this helps, lmk if needed!

3

hey there! have you tried using window functions? they’re super useful for this kinda stuff. maybe something like ROW_NUMBER() or RANK() could help? im curious, what database are you moving to? some have cool features that might make this easier. wanna share more about ur project?

4

Converting an Access query to standard SQL does require some adjustments, especially when dealing with functions like LAST. In my experience, since most SQL dialects don’t support the LAST aggregate directly, the best approach is to use window functions to simulate this behavior. For instance, you can use FIRST_VALUE or LAST_VALUE in combination with appropriate ordering over partitions. A common pattern is to wrap this logic in a CTE or subquery, ensuring you group by key identifiers and then join it back to the main customer table. Below is an example:

WITH LatestInteractions AS (
    SELECT customer_id,
           MAX(follow_up) AS latest_follow_up,
           FIRST_VALUE(contact_date) OVER (PARTITION BY customer_id ORDER BY contact_date DESC) AS recent_contact,
           FIRST_VALUE(connection_type) OVER (PARTITION BY customer_id ORDER BY contact_date DESC) AS recent_connection
    FROM interaction
    GROUP BY customer_id
)
SELECT c.id, c.agent, c.first_name, c.last_name, -- other fields
       li.latest_follow_up, c.account_status, li.recent_contact, li.recent_connection
FROM customer c
JOIN LatestInteractions li ON c.id = li.customer_id
WHERE c.agent LIKE 'REP%'
  AND li.latest_follow_up <= CURRENT_DATE
  AND c.account_status NOT IN (1, 4, 5)
ORDER BY li.latest_follow_up DESC, c.account_status, li.recent_connection;

This approach helped me transition smoothly from Access queries to SQL and can be further customized to your SQL dialect.