Maximum when a value is a maximum?!

[Bobbber]

Sorry for the strange title, but I don't know how else to describe what I'm trying to achieve.

Imagine a table tblTable (all three columns are integers) :
[tt]
ID Int1 Int2
1 10101 100
1 10102 25
1 10102 50
2 10013 65
[/tt]

I want a simpler way of finding the maximum aggregates. I can do this like this :

SELECT ID, MAX(Int2), Xnt1
FROM tblTable
INNER JOIN (SELECT ID AS XID, MAX(Int1) AS Xnt1 FROM tblTable GROUP BY ID)
X ON XID=ID AND Xnt1=Int1
GROUP BY ID

Returning :
[tt]
1 10102 50
2 10013 65
[/tt]

I hope someone understands what I mean, not the same as :

SELECT ID, MAX(Int2), MAX(Int1) FROM tblTable GROUP BY ID

I want the maximum value of Int2 when Int1 is at its maximum. Perhaps there's an easier way than having a sub-query using the same table twice?

Thanks!

Bob

 

[gmmastros]

There is likely to be many different ways you can do this, but your query is the way I would probably code it. You see, when I read it, it makes perfect sense to me.

Other options...

1. You could convert your integers to strings (making sure you 0 pad them to the correct length), then do a single MAX on that. You will then need to split the string in to their original values, which means you probably need to do another join. Since this method requires strings and converts, it will most likely perform slower.

2. You could play around with some math operations where Int1 represents the whole number part of a float, and int2 represents the fractional part of the number. Again... you would need to split the values which would most likely require another derived table.

3. If you are using SQL2005, you could make use of the windowing functions. This one, at least, has the potential to speed up the query. Personally, I find the windowing functions a little difficult to understand, but they're not all bad. One little gotcha with the windowing functions. Unfortunately you can't use it in a where clause, so... again, there is a derived table involved. Here's an example:

Declare @Temp Table(ID Int, Int1 Int, Int2 Int)

Insert Into @Temp Values(1,10101,100)
Insert Into @Temp Values(1,10102,25)
Insert Into @Temp Values(1,10102,50)
Insert Into @Temp Values(2,10013,65)

Select X.Id, X.Int1, X.Int2
From   (
       Select *, 
              Row_Number() OVER(Partition By ID Order By Int1 DESC, Int2 DESC) As Blah
       From   @Temp
       ) As X
Where  X.Blah = 1

I wouldn't necessarily pick this solution over your original query unless it had significantly better performance.

Make sense?

-George

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

 

[bborissov]

No,
all aggregate functions return the result based on GROUP BY clause.
BTW What result you need from the data you posted?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.