Question
Displaying top X results?
I'm brand-new at a job, and working my way through the CF
site, so I apologize in advance for sounding nebulous. Everything
in the site uses FuseActions and stored procedures, and then pulls
specific query language into those procedures.
I've got a query that's returning the desired results, except that we only care about the last 3 years of data. I'm totally blanking on the code string to use to limit this -- I'd prefer to use something dynamic, rather than having to hardcode in a value. I've tried a WHERE YearField IN (Max(YearField), Max(YearField) -1, Max(YearField)-2) and it throws up because it's an aggregate statement. Putting in a GROUP BY didn't help. So I tried a HAVING using the same logic, to no avail.
(FWIW, I've tried it both Max(YearField) - '1' and Max(YearField) -1.)
Is there a TOP 3 type function, or does anyone else have a better idea?
I've got a query that's returning the desired results, except that we only care about the last 3 years of data. I'm totally blanking on the code string to use to limit this -- I'd prefer to use something dynamic, rather than having to hardcode in a value. I've tried a WHERE YearField IN (Max(YearField), Max(YearField) -1, Max(YearField)-2) and it throws up because it's an aggregate statement. Putting in a GROUP BY didn't help. So I tried a HAVING using the same logic, to no avail.
(FWIW, I've tried it both Max(YearField) - '1' and Max(YearField) -1.)
Is there a TOP 3 type function, or does anyone else have a better idea?
AdChoices