# Ordering categories using SQL

**URL:** <https://meta.discourse.org/t/ordering-categories-using-sql/111960>\
**Category:** Development\
**Created:** [March 18, 2019, 9:37pm UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960 "2019-03-18T21:37:48Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![csmu](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/csmu/32/124581_2.png) [@csmu](https://meta.discourse.org/u/csmu)\
**Post date:** [March 18, 2019, 9:37pm UTC](https://meta.discourse.org/t/ordering-categories-using-sql/111960/1 "2019-03-18T21:37:48Z")

</div>

The following code changes the order of categories to be alphanumeric using SQL.

Preconditions.  
Postgres backend

```SQL
UPDATE categories
SET POSITION = subquery.position
FROM (
	SELECT POSITION
	, CASE WHEN category_id = 0 THEN parent_category_id ELSE category_id END category_id 
	, CASE WHEN category_id = 0 THEN parent_category_name ELSE category_name END category_name 
	FROM (
		SELECT ROW_NUMBER - 1 AS POSITION
		, parent_category_id
		, parent_category_name
		, category_id
		, category_name
		FROM ( SELECT row_number() OVER ( ORDER BY parent_category_name, category_name )
		, * FROM (
		SELECT * FROM (
			SELECT id parent_category_id
			, name parent_category_name 
			, 0 category_id
			, cast('' AS text) category_name
			FROM categories 
			WHERE parent_category_id IS NULL ORDER BY name
		) parent_categories
		UNION 
		(
			SELECT child.parent_category_id
			, parent.name parent_category_name 
			, child.id category_id
			, child.name category_name
			FROM categories child
			INNER JOIN categories parent
			ON child.parent_category_id = parent.id
			WHERE child.parent_category_id IS NOT NULL ORDER BY child.name
		) 
		) parents_with_children
		ORDER BY parent_category_name, category_name
		) ordered_parents_with_children
		ORDER BY ROW_NUMBER
	) category_positions_sorted_alphabetically
) subquery
WHERE id = subquery.category_id ;

```

I ran the code by starting up a psql session.

```plaintext
/var/discourse/launcher enter app
su postgres
psql discourse

```

and then executing the SQL.

Running from rails console.

```ruby
ActiveRecord::Base.connection.execute("UPDATE categories SET POSITION = subquery.position FROM ( SELECT POSITION , CASE WHEN category_id = 0 THEN parent_category_id ELSE category_id END category_id , CASE WHEN category_id = 0 THEN parent_category_name ELSE category_name END category_name FROM ( SELECT ROW_NUMBER - 1 AS POSITION , parent_category_id , parent_category_name , category_id , category_name FROM ( SELECT row_number() OVER ( ORDER BY parent_category_name, category_name ) , * FROM ( SELECT * FROM ( SELECT id parent_category_id , name parent_category_name , 0 category_id , cast('' AS text) category_name FROM categories WHERE parent_category_id IS NULL ORDER BY name ) parent_categories UNION ( SELECT child.parent_category_id , parent.name parent_category_name , child.id category_id , child.name category_name FROM categories child INNER JOIN categories parent ON child.parent_category_id = parent.id WHERE child.parent_category_id IS NOT NULL ORDER BY child.name ) ) parents_with_children ORDER BY parent_category_name, category_name ) ordered_parents_with_children ORDER BY ROW_NUMBER ) category_positions_sorted_alphabetically ) subquery WHERE id = subquery.category_id")

```

---

_[View the full topic](https://meta.discourse.org/t/ordering-categories-using-sql/111960)._
