PostgreSQL ORDER BY clause on numerical portion of text column [duplicate]

Question

I have a table that has a column as primary key and the values are

G1,G2,G3,...Gn

I would like to order the data but the problem is when I use the ORDER BY clause it displays data as:

G1,G10,G11... G2,G20,G21, ... G3,G30,G31....

The query I use is:

select * from myTable order by id asc;

@Glorfindel Not a duplicate of the question you mention, even though a solution is there. That question is about creating a new PK for a column with alpha and numerical characters. — Patrick
– Patrick, Commented Sep 16, 2015 at 7:33

Patrick · Accepted Answer · 2015-09-16 07:26:46Z

1

Your id column is obviously of some text data type so the ordering is alphabetical, not by the number. To get it to work, strip the 'G' from the id column when ordering:

SELECT * FROM mytable
ORDER BY right(id, -1)::integer;

answered Sep 16, 2015 at 7:26

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

1 Comment

Shoaib Over a year ago

Great! Thanks! That worked

Collectives™ on Stack Overflow

PostgreSQL ORDER BY clause on numerical portion of text column [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related