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?
-- 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')
- divided by two different sets by
order byits column
- 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 ~