I have found group by having <condition> examples where condition is count(*) > 1
My questions (and wonder) is if the min() or max() are considered as a condition and works fine (since I get compiling errors when using it).
It would make queries simpler... anyway... the problem could be solved making another selection in the selectdo.
Thanx
Félix

The min and max work fine, but you need to tell it which field to get the max of, it does not understand max(*). Also, then, you may need to add some Group By syntax to the SQL.

Check this Wiki Help Topic (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_select) and the related topics.

Sorry, with * I mean any field in the select...
Being more descriptive:

select tdilc995.item.c,
tdilc995.dsca.c,
tdilc995.citg.c,
tdilc995.suno.c,
tdilc995.cuni.c,
min(tdilc995.date.c),
sum(tdilc995.cant.c),
sum(tdilc995.cani.c),
count(tdilc995.item.c),
avg(tdilc995.cani.c)
from tdilc995
where tdilc995._index3 inrange {:date.c.f, :citg.c.f, :suno.c.f, :item.c.f}
and {:date.c.t, :citg.c.t, :suno.c.t, :item.c.t}
group by tdilc995.item.c, tdilc995.dsca.c, tdilc995.citg.c, tdilc995.suno.c, tdilc995.cuni.c having min(tdilc995.date.c)
order by tdilc995.item.c, tdilc995.dsca.c, tdilc995.citg.c, tdilc995.suno.c, tdilc995.cuni.c

tHANX
Félix

I don't believe that you need the Having Min date in the Group clause as you selected Min in the select any this will only give you the Min(Date).

What does the compile error say? I think it's your having clause. The having clause is like a where clause but for the group functions. It needs another side of the equation, like:

having min(tdilc995.date.c) > some date

what I want to select is the first date of the group selection.
that´s why I was not sure if I could set the condition as min o max.
If soo... I could compare to a very old date... but it´s not too nice :(

ok, but that's why you are getting the syntax error, you need 2 sides on the Having.

As Tom said, just having the min in the Select statement will get you only those records with the minimum value on the date. You probably don't need that Having clause at all. I am not sure how having multiple min/max's after that effects things, but I think you will get the minimum date records for each grouping, the count of those records, and the sum/average of those records without the Having clause at all. If not, try just the min call, then add the others 1 by 1 and see how it goes.

Good luck