sqlplus - Greatest values in a column for multiple categories -- oracle11g -

here location1 table:

loc_id     loc_name      loc_state   loc_area 1          abc              ia         600 2          def              fl         700 3          ghi              fl         900 4          jkl              ia         200 5          mno              ny         600 6          cxy              ia         600*   . . 

now, want list greatest loc_area each loc_state , associated loc_name. so, output can this:

loc_state     loc_name     max(loc_area) ia              abc           600 fl              ghi           900 ny              mno           600 

i think starts following don't know if correct.

select loc_state, loc_name, loc_area location1 loc1 loc_area in ( select max(loc_area) location1 loc2 having loc1.loc_state = loc2.loc_state group loc_state); 

can me this?

thanks!

update

the above query gives multiple loc_name same loc_state , max(loc_area).

assuming there there cxy loc_name in ia loc_state same loc_area of 600*.

so output can have both (ia, abc, 600) , (ia, cxy, 600).

i guess acceptable loc_state can have multiple loc_area(s) same value, in case 600 in different loc_name(s).

so, problem solved!

i think a_horse_with_no_name's solution works fine.

select loc_state, loc_name, loc_area location1 loc1 loc_area = (select max(loc_area)                   location1 loc2                   loc1.loc_state = loc2.loc_state); 

thanks again!

your initial statement half way there, co-related condition isn't quite right though. , don't need in or group by in inner query.

so initial statement should this:

select loc_state, loc_name, loc_area location loc1 loc_area = (select max(loc_area)                   location loc2                   loc1.loc_state = loc2.loc_state); 

one alternative using window function avoid querying table twice:

select loc_state,         loc_name,        loc_area (   select loc_state,           loc_name,           loc_area,          dense_rank() on (partition loc_state order loc_area desc) rnk   location ) t rnk = 1;