2

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;
Patrick
  • 29,357
  • 6
  • 62
  • 90
Shoaib
  • 167
  • 2
  • 14
  • @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 Sep 16 '15 at 07:33

1 Answers1

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;
Patrick
  • 29,357
  • 6
  • 62
  • 90