DEV Community

loading...

How do I make paging with ordering by different columns ?

Tingwei
A journey of self-discovery
・1 min read

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')


Enter fullscreen mode Exit fullscreen mode

Approach

  1. divided by two different sets by row_number()...over()
  2. order by its column
  3. union all
  4. 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
Enter fullscreen mode Exit fullscreen mode

idc as a maker to tell whether id2 is null or not.

Thanks for your reading ~

Discussion (0)