I had the same issue where requested order-by involved different data types, and they had to support ASC/DESC.... and pagination.
To get around the datatype consistency that Conner pointed out, I add a row_number() column and actually use that instead in the WHERE clause.
create table dyn_order_by_test
(
name varchar2(10),
date_col date,
address_1 varchar2(50),
address_2 varchar2(50),
city varchar2(20),
state varchar(2),
num_col number
)
Insert into DYN_ORDER_BY_TEST Values ('me', TO_DATE('04/01/2018', 'MM/DD/YYYY'), '123 main street', null, 'NY', 'NY', 345.44);
Insert into DYN_ORDER_BY_TEST Values ('you', TO_DATE('05/01/2018', 'MM/DD/YYYY'), '321 main street', null, 'LA', 'CA', -4);
Insert into DYN_ORDER_BY_TEST values ('you', TO_DATE('05/01/2018', 'MM/DD/YYYY'), '321 main street', null, 'LA', 'CA', -4);
Insert into DYN_ORDER_BY_TEST values ('me', TO_DATE('04/18/2018', 'MM/DD/YYYY'), '123 main street', null, 'NY', 'NY', 1.234);
Insert into DYN_ORDER_BY_TEST values ('me', TO_DATE('04/18/2018', 'MM/DD/YYYY'), '123 main street', null 'NY', 'NY', 1.234);
Insert into DYN_ORDER_BY_TEST Values ('me', TO_DATE('04/18/2018', 'MM/DD/YYYY'), '123 main street', 'line 2', 'NY', 'NY', 1.234);
With zz as
(SELECT
d.name,
d.date_col,
d.address_1,
d.address_2,
d.city,
d.state,
d.num_col,
count(*) over () total_rows ,
case :sort_col
when 'NAME' then row_number() over (order by d.name, d.date_col, d.rowid)
when 'DATE' then row_number() over (order by d.date_col, d.name, d.rowid)
when 'ADDRESS' then row_number() over (order by d.address_1, d.address_2, d.city, d.state, d.rowid)
when 'CITY' then row_number() over (order by d.city, d.state, d.date_col, d.rowid)
when 'STATE' then row_number() over (order by d.state, d.city, d.name, d.rowid)
when 'NUM' then row_number() over (order by d.num_col, d.name, d.rowid)
end rn_sort
FROM dyn_order_by_test d
)
select
zz.name,
zz.date_col,
zz.address_1,
zz.address_2,
zz.city,
zz.state,
zz.num_col,
zz.total_rows,
zz.rn_sort
from zz
where
case when :order_by = 'DESC' then
zz.total_rows - zz.rn_sort + 1
else
zz.rn_sort
end
between ((:curr_page-1)*:page_size)+1 and (:curr_page*:page_size)
order by
case when :order_by = 'DESC' then
zz.total_rows - zz.rn_sort + 1
else
zz.rn_sort
end
The trick here is the order by and where clause for the pagination
order by [rownumber of a] desc
is the same as
order by [total count] - [rownumber of a] + 1
So I can "always" sort ASC, I just change what I order by.
What has also let me do is make an intelligent (by adding more columns in the order by) and deterministic (by adding rowed) sort. By intelligent, I mean when sorted by "State", it is ALSO sorted by City and name. So we humans can actually look for something in the returned list.
By deterministic, if I sorted by State, and there are more than 1 page of the same state, as we go from page 1 to page 2, there are no repeats, and every one is shown once.
You still need to scan the entire table (have to, to be able to sort it), but you are getting the dynamic ordering.