DISTINCT ROW MAXVALUE HELP

[westex96]

I AM A BEGINNER AT SQL. : )

IF THE X COLUMN HAS EQUAL DATA ENTRIES (10...10 EXAMPLE) AND THE Y COLUMN HAS EQUAL
DATA ENTRIES (7...7 EXAMPLE) THEN I WOULD LIKE TO SELECT OUT THE LARGEST VALUE IN THE POWER COLUMN. I APPRECIATE YOUR HELP IN ADVANCE.

ORIGINAL DATA

ID X Y Power SECTOR
1 10 7 1 CV301B
2 10 7 2 CV302B
3 10 7 4 CV300A
4 10 7 23 CV300C
5 10 7 12 CV302C
6 11 8 9 CV301A
7 11 8 3 CV300B
8 11 8 7 CV301B
9 11 8 78 CV302A
10 11 8 4 CV301C
11 11 8 3 CV302C
12 11 8 1 CV302B


DESIRED OUTPUT

ID Lat Long Power SECTOR
4 10 7 23 CV300C
9 11 8 78 CV302A

 

[r937]

select t.ID
     , t.X   as Lat
     , t.Y   as Long 
     , t.Power 
     , t.SECTOR
  from daTable as t
inner
  join (
       select X        
            , Y        
            , max(Power) as maxPower    
         from daTable
       group
           by X        
            , Y 
       ) as m
    on m.X = t.X
   and m.Y = t.Y 
   and m.maxPower = t.Power

r937.com | rudy.ca

 

[westex96]

R937....THANK YOU SO MUCH! IT WORKED PERFECTLY! YOU HAVE SAVED ME HOURS OF WORK! I TRULY APPRECIATE YOU!

I BELIEVE I UNDERSTAND THE T AND M ADDED NICE APPROACH!

CHEERS,

RAY