sql - ORA-00905: missing keyword when using Case in order by -
i have below query, if edit date not null, recent record needs returned , should randomized else records should randomized. tried below order , getting missing keyword error.
select * ( select c.id,c.edit_date, c.name,l.title tablea c, tableb l c.id = l.id , c.published_ind = 'y' , lc.type_id != 4 , trim(c.img_file) not null order dbms_random.value ) rownum = 1 order case when c.edit_date = 'null' dbms_random.value else dbms_random.value, c.edit_date desc end
if correct, try record per id either highest date (a random 1 if more records same date exists) or null date (again random 1 when more null records same id exists.
so assuming data
id edit_date text ---------- ------------------- ---- 1 01.01.2015 00:00:00 1 01.01.2016 00:00:00 b 1 01.01.2016 00:00:00 c 2 01.01.2015 00:00:00 d 2 01.01.2016 00:00:00 e 2 f 2 g you expect either b or c id =1 , either f or g id = 2.
this query it. features used ordering nulls first , adding random value last ordering column - random result if preceeding columns same..
with dta ( select 1 id, to_date('01012015','ddmmyyyy') edit_date, 'a' text dual union select 1 id, to_date('01012016','ddmmyyyy') edit_date, 'b' text dual union select 1 id, to_date('01012016','ddmmyyyy') edit_date, 'c' text dual union select 2 id, to_date('01012015','ddmmyyyy') edit_date, 'd' text dual union select 2 id, to_date('01012016','ddmmyyyy') edit_date, 'e' text dual union select 2 id, null edit_date, 'f' text dual union select 2 id, null edit_date, 'g' text dual), dta2 ( select id, edit_date, text, row_number() on (partition id order edit_date desc nulls first, dbms_random.value) rn dta) select * dta2 rn = 1 order id ; id edit_date text rn ---------- ------------------- ---- ---------- 1 01.01.2016 00:00:00 b 1 2 f 1 hopefully can re-use thhe idea if need bit different result...
Comments
Post a Comment