Question 332165 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – I want get SQL results in correct ascending order. How to get them?

I have a SQL query like this:

select age_group, sum(member_count), sum(discharge_count) 
from newtable 
group by age_group 
order by age_group;

It is returns these results:

age_group member_count discharge_count
18-64 Years 150 245
6-17 Years 185 325
65 and Above Years 125 230

But I want results like this instead:

age_group member_count discharge_count
6-17 Years 185 325
18-64 Years 150 245
65 and Above Years 125 230
Tags:

2

Answers


  1. 0

    String ordering doesn’t know numbers, so what happened is that, first it compares the first letter, then the second and so on. And since the letter "6" comes after "1" in the alphabet, your results get messed up. You should extract the numbers at the beginning of the age_group field (with two SPLIT_PARTs) and convert them to numeric type (with CAST):

    select age_group, sum(member_count), sum(discharge_count) 
from newtable 
group by age_group 
order by CAST(SPLIT_PART(SPLIT_PART(age_group, ' ', 1), '-', 1) AS INTEGER);
    Login or Signup to reply.
  2. 0

    You can substring the value until the position – and sort the result,

    select age_group, sum(member_count), sum(discharge_count) 
from newtable 
group by age_group 
order by substring(age_group, 0, position('-' in age_group) -1)::int;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search