Redshift ORDER BY is used to sort the output rows in the result set. You can sort the output rows in ascending or descending order. By default, the sorting order is ascending. You can sort output rows on the basis of single column, multiple columns or even combination of ascending and descending for different columns. You can also use derived columns created in select statement in order by clause.
To sort output rows in ascending order use "asc" keyword.
To sort output rows in descending order use "desc" keyword.
Redshift ORDER BY on single column in ascending order
ASCENDING ORDER means sort values from smaller to bigger values. So smaller values will come before and bigger values at last.
DESCENDING ORDER is the opposite of ascending in which biggest value comes first and smallest values comes at last.
select catid, catgroup, catname, catdesc from category order by catid asc;
OUTPUT | |||
---|---|---|---|
catid | catgroup | catname | catdesc |
1 | Sports | MLB | Major League Baseball |
2 | Sports | NHL | National Hockey League |
3 | Sports | NFL | National Football League |
4 | Sports | NBA | National Basketball Association |
5 | Sports | MLS | Major League Soccer |
6 | Shows | Musicals | Musical theatre |
7 | Shows | Plays | All non-musical theatre |
8 | Shows | Opera | All opera and light opera |
9 | Concerts | Pop | All rock and pop music concerts |
10 | Concerts | Jazz | All jazz singers and bands |
11 | Concerts | Classical | All symphony, concerto, and choir concerts |
In the above query "asc" keyword is optional. If you want to sort output data in DESCENDING format then use "desc" keyword with the column name in ORDER BY clause.
Redshift ORDER BY on single column in descending order
select catid, catgroup, catname, catdesc from category order by catid desc;
OUTPUT | |||
---|---|---|---|
catid | catgroup | catname | catdesc |
11 | Concerts | Classical | All symphony, concerto, and choir concerts |
10 | Concerts | Jazz | All jazz singers and bands |
9 | Concerts | Pop | All rock and pop music concerts |
8 | Shows | Opera | All opera and light opera |
7 | Shows | Plays | All non-musical theatre |
6 | Shows | Musicals | Musical theatre |
5 | Sports | MLS | Major League Soccer |
4 | Sports | NBA | National Basketball Association |
3 | Sports | NFL | National Football League |
2 | Sports | NHL | National Hockey League |
1 | Sports | MLB | Major League Baseball |
Redshift ORDER BY multiple columns
You can specify multiple columns in ORDER BY clause and also mention asc or desc sorting order you want for each columns. Default value is asc so if you do not mention anything it is assumed to be ascending.
select catid, catgroup, catname, catdesc from category order by catgroup desc, catname asc;
OUTPUT | |||
---|---|---|---|
catid | catgroup | catname | catdesc |
1 | Sports | MLB | Major League Baseball |
5 | Sports | MLS | Major League Soccer |
4 | Sports | NBA | National Basketball Association |
3 | Sports | NFL | National Football League |
2 | Sports | NHL | National Hockey League |
6 | Shows | Musicals | Musical theatre |
8 | Shows | Opera | All opera and light opera |
7 | Shows | Plays | All non-musical theatre |
11 | Concerts | Classical | All symphony, concerto, and choir concerts |
10 | Concerts | Jazz | All jazz singers and bands |
9 | Concerts | Pop | All rock and pop music concerts |
Redshift ORDER BY derived column
You can also use derived columns in ORDER BY clause to determine the sort order for output rows. It is recommended to use ALIAS name for Derived columns always.
select catid, catdesc, case when catdesc like '%Baseball%' then 'Baseball' when catdesc like '%Basketball%' then 'Basketball' else 'Other Sports' end as Sports from category where catgroup = 'Sports' order by Sports;
OUTPUT | ||
---|---|---|
catid | catdesc | sports |
1 | Major League Baseball | Baseball |
4 | National Basketball Association | Basketball |
3 | National Football League | Other Sports |
2 | National Hockey League | Other Sports |
5 | Major League Soccer | Other Sports |