CODEWITHSUNDEEP
sqloracle-databasecase
user747291
user747291

Reputation: 821

ORA-00905: missing keyword when using Case in order by

I have the below query, where if the edit date is not null, then the most recent record needs to be returned and also should be randomized else the records should be randomized. I tried the below order by , but I am getting the missing keyword error. 

SELECT * FROM ( SELECT c.id,c.edit_date, c.name,l.title
                                          FROM tableA c, tableb l
                                         WHERE c.id = l.id
                                           AND c.published_ind = 'Y'
                                           AND lc.type_id != 4 
                                           AND TRIM(c.img_file) IS NOT NULL
                                        ORDER BY DBMS_RANDOM.VALUE
                                   )
                         WHERE ROWNUM = 1

     order by case  when c.edit_date = 'null' 
           then DBMS_RANDOM.VALUE
           else DBMS_RANDOM.VALUE, c.edit_date desc
     end

Upvotes: 0

Views: 499

Answers (2)

AlexSmet
AlexSmet

Reputation: 2161

Statement WHERE always apply before statement ORDER BY. So in your query at first will applied WHERE ROWNUM = 1 and only after that will applied order by case ... for single record. Perhaps you need add another subquery that at first execute ORDER BY, get rowset in proper order and after that execute WHERE ROWNUM = 1 to select single row.

Statment ORDER BY ... DBMS_RANDOM.VALUE, c.edit_date look strange. In fact, recordset will be sorted by DBMS_RANDOM.VALUE and if rowset has couple of rows have equal DBMS_RANDOM.VALUE we additionally will sort them by c.edit_date.

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21075

If I get you correct, you try to get a record per ID with either the highest date (a random one if more records with the same date exists) or with a NULL date (again random one when more NULL records with the same ID exists.

So assuming this data

        ID EDIT_DATE           TEXT
---------- ------------------- ----
         1 01.01.2015 00:00:00 A    
         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 for ID =1 and either F or G for ID = 2.

This query do it. The features used are ordering with NULLS FIRST and adding a random value as a last ordering column - to get random result if all preceeding columns are the same..

with dta as (
select  1 id, to_date('01012015','ddmmyyyy') edit_date, 'A' text from dual union all
select  1 id, to_date('01012016','ddmmyyyy') edit_date, 'B' text from dual union all
select  1 id, to_date('01012016','ddmmyyyy') edit_date, 'C' text from dual union all
select  2 id, to_date('01012015','ddmmyyyy') edit_date, 'D' text from dual union all
select  2 id, to_date('01012016','ddmmyyyy') edit_date, 'E' text from dual union all
select  2 id, NULL edit_date, 'F' text from dual union all
select  2 id, NULL edit_date, 'G' text from dual),
dta2 as (
select ID, EDIT_DATE, TEXT,
row_number() over (partition by ID order by edit_date DESC NULLS first, DBMS_RANDOM.VALUE) as rn
from dta)
select *
from dta2 where rn = 1
order by id
;

        ID EDIT_DATE           TEXT         RN
---------- ------------------- ---- ----------
         1 01.01.2016 00:00:00 B             1 
         2                     F             1

Hopefully you can re-use thhe idea if you need a bit different result...

Upvotes: 1

Related Questions