DEV Community

Rayen
Rayen

Posted on

6 1

Easy way to maintain an ordered list in SQL database

This guide explains how to efficiently manage task ordering using a SQL table with an order_index column. The approach ensures unique ordering and allows seamless reordering of tasks.

Implementation

Table Definition

We create a temporary table called tasks with an order_index column to maintain task order:

CREATE TEMPORARY TABLE tasks(
  id integer primary key,
  name varchar not null,
  order_index DOUBLE PRECISION DEFAULT EXTRACT(epoch FROM CURRENT_TIMESTAMP)
);
ALTER TABLE tasks ADD CONSTRAINT "order_index_UQ" UNIQUE(order_index);
Enter fullscreen mode Exit fullscreen mode

Explanation

  • The order_index column is assigned a default value based on the current timestamp, ensuring each task gets a unique order value at creation.
  • The UNIQUE constraint guarantees that no two tasks share the same order_index.

Inserting a new task

By default, new tasks receive the highest order index (latest timestamp). If you want to insert a task with the lowest order, you can multiply the timestamp by -1:

INSERT INTO tasks (name, order_index)
VALUES ('Task 1', -1 * EXTRACT(EPOCH FROM CURRENT_TIMESTAMP));
Enter fullscreen mode Exit fullscreen mode

Why Multiply by -1?

  • This ensures the task appears at the beginning of the list.
  • Positive timestamps naturally sort later, while negative timestamps sort earlier.

Updating Task Order

To reorder a task, the client must provide:

  • taskAboveId: ID of the task above the target task
  • taskBelowId: ID of the task below the target task
  • taskId: ID of the task being moved

Using these, the new order_index is calculated as the average of the surrounding task indices:

const taskAbovePosition = taskAbove ? taskAbove.order_index : taskBelow.order_index + 1;
const taskBelowPosition = taskBelow ? taskBelow.order_index : taskAbove.order_index - 1;
const newPosition = (taskAbovePosition + taskBelowPosition) / 2;
Enter fullscreen mode Exit fullscreen mode

Why Use Averaging?

  • Ensures the new task order remains between its neighbors without requiring a full reorder.
  • Helps avoid conflicts and reuses available space in the floating-point range.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (6)

Collapse
 
bilelsalemdev profile image
bilel salem • • Edited

This post is written by my friend @rayenmansouri after a lot of search and a lot of tries.
Big thanks to his efforts.
Thank you for sharing your thoughts .

Collapse
 
xghaith profile image
x-ghaith •

Great Job !

Collapse
 
youssefbrr profile image
Youssef Bourourou •

Gj mate 🎉

Collapse
 
benjeddou_monem_68600c6c8 profile image
benjeddou monem •

Very insightful thank you you for sharing.

Collapse
 
kastouri_hazem profile image
Hazem Kastouri •

How clever ! thanks for sharing Rayen 🤯🔥

Collapse
 
abdelkader_bouzomita_c34e profile image
Abdelkader Bouzomita •

thanks for sharing Rayen

đź‘‹ Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay