Is it a MIN function? 2

[Katya85S]

The select query consists of number of INNER JOINs and brings several records per CustomerAccount different by 2 fields: Day field and Balance field.
What I need is: 1 record per customer. The record should have the lowest balance of the selected Customer and the earliest day.

I thought min and max functions could do that,
I’ve tried etc.

SELECT Account, CustomerName, min(AccBalance) FROM ….
GROUP BY Account, CustomerName, AccBalance

but I was getting the same number of records per customer as when I didn’t use the min function.

What am I doing wrong and how to have displayed just the lowest Balance and the earliest day?

Thank you all in advance...

 

[RiverGuy]

Take off AccBalance in your GROUP BY

 

[gmmastros]

Remove AccBalance from the group by clause.

GROUP BY Account, CustomerName[!], AccBalance[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

When using an aggregate function, you need to be a little careful, because you could inadvertently bring back the wrong data.

Example aggregate functions are: Min, Max, Sum, etc...

When you use multiple aggregate functions in a query, you need to realize that it won't necessarily return the data from the same column.

For example, you said you wanted the lowest balance and the earliest date. If those records don't correspond to the same row, you will still get the lowest balance and the earliest date.

In the example I show below, I create a table variable to 'dummy up' some data. This means you can copy the code block to Query Analyzer windows and run it without it affecting any data in real tables.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Account [COLOR=blue]int[/color], CustomerName [COLOR=blue]varchar[/color](20), AccBalance [COLOR=blue]Decimal[/color](10,4), AccountDate [COLOR=#FF00FF]DateTime[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'George'[/color], 212.34, [COLOR=red]'20080101'[/color]) [COLOR=green]-- Jan 1, 2008
[/color][COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'George'[/color], 120.45, [COLOR=red]'20080201'[/color]) [COLOR=green]-- Feb 1, 2008
[/color][COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'George'[/color], 315.20, [COLOR=red]'20080301'[/color]) [COLOR=green]-- Mar 1, 2008
[/color][COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'George'[/color], 265.79, [COLOR=red]'20080401'[/color]) [COLOR=green]-- Apr 1, 2008
[/color]
[COLOR=blue]Select[/color] Account, 
       CustomerName, 
       [COLOR=#FF00FF]Min[/color](AccBalance) [COLOR=blue]As[/color] MinAccBalance, 
       [COLOR=#FF00FF]Min[/color](AccountDate) [COLOR=blue]As[/color] MinAccountDate
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Group[/color] [COLOR=blue]BY[/color] Account, CustomerName

Looking at the data, it's easy to see that the min AccBalance is 120.45, and the earliest date is Jan 1, 2008. But, also realize that each value exists in a different row. Of course, it's entirely possible that this is the results you want, but if it's not, then you have a problem.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Katya85S]

Many thanks to you guys!
George, a special thank for your explanation in the second post. I appreciate it very much.