>>Hi,
>>
>>Several of my reports use MAX(CHAR_FIELD) AS CHAR_FIELD when using GROUP BY clause. This, of course, to make sure that the GROUP BY does not "break" the report. My question is, is it wrong to have many MAX() for this purpose? Should I change the reports such as to minimize using MAX() around char type fields?
>>
>>TIA.
>
>It depends on what exactly do you want to get. If you want to get the related record's info, then check these blog posts:
>
>
Optimizing TOP N per Group Queries - blog by Itzik Ben-Gan explaining various optimization ideas
>
>
Including an Aggregated Column's Related Values - Erik and mine blog presenting several solutions of the problem with explanations for each
>
>
Including an Aggregated Column's Related Values - Part 2 - my blog post with use cases for the previous blog
I read (very quickly) one of your blogs and really liked how correlated subquery can be applied to my case. It even eliminates the need for HAVING clause (that I was never really comfortable). I created a simple test with the correlated query and seems to return correct results. Thank you very much. Do you want me to post my test/sample SQL Select so that you can take it apart? <g>.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
