RE: SQL - select the record with the highest count value within afield

[AGlauser@xxxxxxxxxxxx]

> Forgive me.  Can I make it a bit more challenging by adding one more
> field to the data? 
> Example:
> Data:
> FIELD1   FIELD2    FIELD3    COUNTFIELD
> AAA      A12       D1          5
> AAA      A13       D5          2
> AAA      A14       D2          1
> BBB      B17       CC          5
> BBB      B13       DC          5
> BBB      B15       D1          1
>
> Result needed (Assuming FIELD1 is the field where the highest 
> COUNTFIELD need to select from)
> FIELD1   FIELD2    FIELD3   COUNTFIELD
> AAA      A12       D1          5
> BBB      B17       CC          5
>
> Or
>
> FIELD1   FIELD2    FIELD3   COUNTFIELD
> AAA      A12       D1          5
> BBB      B13       DC          5

I think to add a third field as you mentioned, you would just need to 
extend Birgitta's example with one more Common Table Expression:

     c as (Select  b.Field1, b.maxfield2, Maxcount, Max(Field3) as 
MaxField3
              from b join MyTable x
                on b.Field1 = x.Field1 and b.maxfield2=x.field2 and 
a.CountField = MaxCount)
select y.*
from MyTable y join c 
     on y.Field1 = c.Field1 and y.Field2 = c.MaxField2 and y.field3 = 
c.MaxField 3 and CountField = MaxCount

I think a simliar technique would work for n fields, but as some point 
this becomes really ugly and you probably want to consider using record 
I/O and SETGT.

HTH,
Adam





#####################################################################################
Attention:
The above message and/or attachment(s) is private and confidential and is 
intended 
only for the people for which it is addressed. If you are not named in the 
address 
fields, ignore the contents and delete all the material. Thank you. Have a nice 
day.

For more information on email virus scanning, security and content
management, please contact administrator@xxxxxxxxxxxx
#####################################################################################