Thursday, 19 December 2013

Small SQL puzzle - discrete top 3

Being the APEX geek I am I've created a page that lists all the entrants so far to the Packt Publishing Phonegap ebook giveway

Check it out here
http://apex.oracle.com/pls/apex/f?p=73000:22

I'll post more about how I've extrapolated records from derived column values, but I am looking for suggestions to improve the 'prize?' column

This is the current calculation - basically highlighting first three records in output.
case when row_number() over (order by dbms_random.value()) <= 3 then 'maybe?' end prize
But what if two of those records share the same name?

Here is some test data, where socrates, plato & yeats need to win the prize
with data as
 (select 'socrates'  name, 1 entry, 1 ord from dual union all
  select 'plato'     name, 1 entry, 2 ord from dual union all
  select 'socrates'  name, 2 entry, 3 ord from dual union all
  select 'yeats'     name, 1 entry, 4 ord from dual union all
  select 'aristotle' name, 1 entry, 5 ord from dual)
select case when row_number () over (order by ord)  <= 3 then 'maybe?' end prize
  ,name
from data
order by ord
Go nuts

No comments: