Incorrect ORDER BY PostgreSQL

Question

I'm having a weird problem when I'm trying to order my column. The column is different than usual tables I'm working with where the43 is usually a comma in the numbers. Here the numbers have no comma and when I order the column I get something like

company_id
10097
1024
10304
151

Any help? Ideally I'd like to turn these numbers into numbers with commas so it's consistent with other tables when I do joins later

Column data type? Integer would help, but I'd guess it's varchar here... — jarlh
– jarlh, Commented Apr 15, 2021 at 12:22
Never store numbers in text columns - you just found out one reason why it's a bad idea. — user330315
– user330315, Commented Apr 15, 2021 at 12:26
Numbers and comma's in a single field, stored as text. Sounds like a major problem, and ordering is just one of them. Please fix your datamodel asap, it will save you a lot of time and frustration. — Frank Heikens
– Frank Heikens, Commented Apr 15, 2021 at 12:53

Gordon Linoff · Accepted Answer · 2021-04-15 12:23:22Z

4

Presumably, the values are strings. You can sort them in one of two ways. The first is the "string" way:

 order by length(company_id), company_id

The second is to convert to a number:

order by company_id::numeric

answered Apr 15, 2021 at 12:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Incorrect ORDER BY PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related