DEV Community

Tingwei
Tingwei

Posted on

How do I make paging with ordering by different columns ?

#sql #database #postgres #webdev

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 ~

Top comments (0)