I need to sort out two sets of data. The order rules are followed by first the column "id2" is null or not.
After sorting out, One set of data should be ordered by the column dt2 when the column id2 is null. The other should be also ordered by the column dt3 when the column id2 is not null.
The tricky part is to combine two sets of data together.
I know ...union all... but how?
Basic setup
-- create table
create table MyTable
(
id bigint,
id2 bigint null,
dt2 date null,
dt3 date null
)
-- insert value
insert into MyTable (id, id2, dt2, dt3) values
(1, null, '2019-10-01', '2019-11-01'),
(2, null, '2019-09-05', '2019-11-02'),
(3, 1, '2019-10-12', '2019-09-28'),
(4, 2, '2019-10-02', '2019-10-28'),
(5, 3, '2019-09-30', '2019-11-03')
Approach
- divided by two different sets by
row_number()...over() -
order byits column union all- select and
offset...fetch next ... rows only
select id, id2, dt2, dt3 from
(
select id, id2, 1 as idc, dt2, dt3, row_number() over (order by dt3 desc) as rnum
from MyTable
where id2 is null
union all
select id, id2, 2 as idc, dt2, dt3, row_number() over (order by dt2 desc) as rnum
from MyTable
where id2 is not null
) as m
order by m.idc, m.rnum -- order by
offset 0 -- 0 is the skip count
fetch next 2 rows only -- 2 is the page size
idc as a maker to tell whether id2 is null or not.
Thanks for your reading ~
Top comments (0)