CODEWITHSUNDEEP
sqloracle-database
Zeus
Zeus

Reputation: 6566

Max analytical function plus windowed ordering is not working as expected

I have a table with status and location, the following is the data. I'd like to get max status partitioned by location using custom ordering. Any idea what needs to change? Right now it is giving only max value, irrespective of the ordering i mentioned.

The custom ordering is 1 > 3 > 2

status | location
  1    | 11 
  2    | 11 
  2    | 12 
  3    | 12
  3    | 11

Expected result for location 11 : 1 Expected result for location 12 : 3

Query: 

select max(status) over (partition by location order by decode(status, '1',6,'3',5,'2',4,3) 
                         rows between unbounded preceding and unbounded following) mx from items;

http://sqlfiddle.com/#!4/ed9e7e/13

create table items
( status varchar2(1), location number(9)
);

insert into items values('1',123);

insert into items values('2',123);

insert into items values('3',123);

insert into items values('4',123);

Upvotes: 0

Views: 45

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

I suggest that using a simple GROUP BY might prove easier:

SELECT LOCATION,
       DECODE(MAX(ORDERING), 6, '1', 5, '3', 4, '2', 3) AS STATUS
  FROM (SELECT LOCATION,
               STATUS AS STATUS,
               DECODE(STATUS, '1', 6, '3', 5, '2', 4, 3) AS ORDERING
          FROM ITEMS)
  GROUP BY LOCATION
  ORDER BY LOCATION

db<>fiddle here

Upvotes: 1

Isaac
Isaac

Reputation: 3363

I see a couple of issues. Your decode doesn't seem to match up with you are are saying you want. Secondly, I don't think MAX() is the function you want to use because it is returning maximum status without respect to your custom order. Instead you should assign row numbers partitioned by location and ordered by your custom sort order. Then pick all the rows with where row number is 1.

create table items
( status varchar2(1), location number(9)
);

insert into items values('1',11);
insert into items values('2',11);
insert into items values('2',12);
insert into items values('3',12);
insert into items values('3',11);

select x.location, x.status
from (
    select ROW_NUMBER() over (partition by location order by decode(status, '1',1,'2',3,'3',2,4)) as rn, 
           status, location from items) x
where x.rn = 1

SQL Fiddle.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think you want first_value():

select first_value(status) over (partition by location
                                 order by decode(status, '1', 6, '3', 5, '2', 4, 3) 
                                ) mx
from items;

I'm not a big fan of decode(), but it is a concise way to express what you want. I prefer case expressions.

Upvotes: 2

Related Questions