And special thanks to all the great people who voted for me! 🙂
I want to list all voters for all nomenees in one page: https://orasql.org/odevchoice/all-the-voters.html
The query (using xt_http of course :):
[sourcecode language=”sql”]
with
finalists(category, userid, name) as (
— SQL~ Voting:
—— —————— ————————————————————————————-
select ‘SQL’ , 6899, ‘Stew Ashton ‘ from dual union all
select ‘SQL’ , 6900, ‘Sean Stuber ‘ from dual union all
select ‘SQL’ , 6901, ‘Sayan Malakshinov’ from dual union all
select ‘SQL’ , 6902, ‘Matthias Rogel ‘ from dual union all
select ‘SQL’ , 6903, ‘Kim Berg Hansen ‘ from dual union all
select ‘SQL’ , 6904, ‘Justin Cave ‘ from dual union all
select ‘SQL’ , 6905, ‘Erik Van Roon ‘ from dual union all
select ‘SQL’ , 6906, ‘Emrah Mete ‘ from dual union all
— PL/SQL~ Voting
—— —————— ————————————————————————————-
select ‘PL/SQL’ , 6907, ‘Sean Stuber ‘ from dual union all
select ‘PL/SQL’ , 6908, ‘Roger Troller ‘ from dual union all
select ‘PL/SQL’ , 6909, ‘Patrick Barel ‘ from dual union all
select ‘PL/SQL’ , 6910, ‘Morten Braten ‘ from dual union all
select ‘PL/SQL’ , 6911, ‘Kim Berg Hansen ‘ from dual union all
select ‘PL/SQL’ , 6912, ‘Bill Coulam ‘ from dual union all
select ‘PL/SQL’ , 6913, ‘Adrian Billington’ from dual union all
— ORDS ~ Voting
—— —————— ————————————————————————————-
select ‘ORDS’ , 6881, ‘Tim St. Hilaire ‘ from dual union all
select ‘ORDS’ , 6882, ‘Morten Braten ‘ from dual union all
select ‘ORDS’ , 6883, ‘Kiran Pawar ‘ from dual union all
select ‘ORDS’ , 6884, ‘Dimitri Gielis ‘ from dual union all
select ‘ORDS’ , 6885, ‘Dietmar Aust ‘ from dual union all
select ‘ORDS’ , 6886, ‘Anton Nielsen ‘ from dual union all
— APEX ~ Voting
—— —————— ————————————————————————————-
select ‘ORDS’ , 6887, ‘Trent Schafer ‘ from dual union all
select ‘ORDS’ , 6888, ‘Paul MacMillan ‘ from dual union all
select ‘ORDS’ , 6889, ‘Morten Braten ‘ from dual union all
select ‘ORDS’ , 6890, ‘Kiran Pawar ‘ from dual union all
select ‘ORDS’ , 6891, ‘Karen Cannell ‘ from dual union all
select ‘ORDS’ , 6893, ‘Juergen Schuster ‘ from dual union all
select ‘ORDS’ , 6894, ‘Jari Laine ‘ from dual union all
— DatabaseDesign ~ Voting
—— —————– ————————————————————————————-
select ‘DbDesign’, 6896, ‘Michelle Kolbe ‘ from dual union all
select ‘DbDesign’, 6897, ‘Mark Hoxey ‘ from dual union all
select ‘DbDesign’, 6898, ‘Heli Helskyaho ‘ from dual union all
select ‘DbDesign’, 6919, ‘Rob Lockard ‘ from dual
)
select
f.category
,f.name as Nominee
,row_number()over(partition by f.category,f.name
order by
case
when regexp_like(t.column_value,’^user\d+$’) then 2
when regexp_like(t.column_value,’^\d+$’) then 3
when regexp_like(t.column_value,’\w{6}-\w{4}-\w{4}-\w{4}-\w{12}’) then 4
else 1
end
) n
,t.column_value as VoterName
from finalists f,
table(
xt_http.get_matches(
pUrl => ‘https://community.oracle.com/voting-history.jspa?ideaID=’||to_char(f.userid,’fm0000′)||’&start=0&numResults=1000’
,pPattern => ‘alt="([^"]+)"’
,pGroup => 1
)
) t
order by 1,2,3,4
[/sourcecode]