Tom,

I need your guidance in solving this problem:
For a unique combination of state, city and store, I need to find the highest
selling item code (along with the key items as well as the item quantity. If two or more items sell equal amounts for a given ST, City, Store combination, then I need the lowest item code along witht the keys and the quantity. I have done this using an ugly looking PL/SQL procedure. There has got to be a way to
solve this using SQL/Plus only.
Could you please suggest a solution in SQL/Plus? I'd be highly obliged.


CREATE TABLE sales (state varchar2 (2), city varchar2(3), store varchar2(2),
itemcode number);
insert into sales values('MD', '100', '01',40);
insert into sales values('MD', '100', '01',40);
insert into sales values('MD', '101', '10',50);
insert into sales values('MD', '101', '10',50);
insert into sales values('MD', '101', '10',50);
insert into sales values('MD', '101', '10',51);
insert into sales values('MD', '101', '10',51);
insert into sales values('MD', '101', '10',51);
insert into sales values('MD', '102', '20',51);
insert into sales values('MD', '102', '20',51);
insert into sales values('MD', '102', '20',52);
insert into sales values('MD', '102', '20',52);
insert into sales values('MD', '102', '20',52);
insert into sales values('MD', '102', '20',52);

select * from sales;

ST CITY STORE ITEMCODE
-- ---- ----- --------
MD 100 01 40
MD 100 01 40
MD 101 10 50
MD 101 10 50
MD 101 10 50
MD 101 10 51
MD 101 10 51
MD 101 10 51
MD 102 20 51
MD 102 20 51
MD 102 20 52
MD 102 20 52
MD 102 20 52
MD 102 20 52 ...

What I need is as follows:

ST CITY STORE ITEMCODE
-- ---- ----- --------
MD 101 10 40 (Item 40 - the only item in St+City+Store combination (MD 101 10))
MD 101 10 50 (Count of items 50 and 51 is the same but 50 is the "lower" of the two itemcodes)
MD 102 20 52 (Count of items 52 = 4 Count of item 51 = 2; take the item with greater count)

ops$tkyte%ORA11GR2> select *
  2    from (
  3  select state, city, store, itemcode, row_number() over (partition by state, city, store order by cnt desc, itemcode asc) rn
  4    from (
  5  select state, city, store, itemcode, count(*) cnt
  6    from sales
  7   group by state, city, store, itemcode
  8         )
  9             )
 10   where rn = 1
 11   order by state, city, store
 12  /

ST CIT ST   ITEMCODE         RN
-- --- -- ---------- ----------
MD 100 01         40          1
MD 101 10         50          1
MD 102 20         52          1