Have Geek Brain

SQL statement HAVING MAX (some + thing) = some + thing

I am having problems with Microsoft Access 2003, it complains about this statement:

select cardnr
from change
where  year(date)<2009
group by cardnr
having max(time+date) = (time+date) and cardto='VIP'

What I want to do is change for each individual card in the table - find the row with the latest (time + date) before 2009 and then just select the rows with cardto = 'VIP'.

This validator says it's ok, Access says it's not ok.

This is the message I receive: "you tried to execute a query that does not include the specified expression" max (time + date) = time + date and cardto = "VIP" and cardnr = 'as part of a function aggregate ".

Can anyone please explain what I am doing wrong and the correct way to do it? Thanks to

Note. The field and table names are translated and don't clash with any reserved words, I have no problem with the names.

+2


a source to share


1 answer


Try to think of it this way: HAVING is applied after the aggregation is complete. Therefore, it cannot compare to non-aggregated expressions (neither for time + date nor for cardto).

However, to get the latter (the principle is the same for getting rows associated with other aggregate functions like weel) time and date, you can do something like:

SELECT cardnr
FROM change main
WHERE time+date IN (SELECT MAX(time+date) 
                    FROM change sub
                    WHERE sub.cardnr = main.cardnr AND 
                          year(date)<2009 
                          AND cardto='VIP')


(assuming the date in your time field is the same for all records; having two fields for date / time is not in your best interest, and also using reserved words for field names can backfire in certain cases)

This works because the subquery is only filtered by the records you are interested in from the outer query.

Applying the same year (date) <200 and cardto = 'VIP' to an external query can improve performance.

+3


a source






More articles:


All Articles