Custom sorting in sql

Question

I have a table with a column named category. the data in it is 1, 2, 3 so every row has a category.

I need sort data by category like this

1,2,3,1,2,3,1,2,3,....

then if some category finished sorting continues like this

1,2,3,1,2,3,1,3,1,3,1,1,1,1,....

I am using PostgreSQL.

thanks for your answers

Gordon Linoff · Accepted Answer · 2019-07-29 11:59:22Z

2

You can use window functions:

order by row_number() over (partition by category order by category)

You can specify whatever you want for the order by. For instance, if you want a random ordering:

order by row_number() over (partition by category order by random())

answered Jul 29, 2019 at 11:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mustafa BYKSY · Accepted Answer · 2019-07-29 12:13:09Z

1

You can use like this:

SELECT
   c,
   RANK () OVER ( 
      ORDER BY c 
   ) rank_number 
FROM
   ranks;

answered Jul 29, 2019 at 12:13

Mustafa BYKSY

939 bronze badges

Red Devil · Accepted Answer · 2019-07-29 12:17:53Z

0

select ID from (
select *,row_number() over ( partition by id order by id ) rn from Yourtable  
) I
order by  rn,id

answered Jul 29, 2019 at 12:17

Red Devil

2,4135 gold badges30 silver badges46 bronze badges