Redshift ORDER BY

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
catidcatgroupcatnamecatdesc
1SportsMLBMajor League Baseball
2SportsNHLNational Hockey League
3SportsNFLNational Football League
4SportsNBANational Basketball Association
5SportsMLSMajor League Soccer
6ShowsMusicalsMusical theatre
7ShowsPlaysAll non-musical theatre
8ShowsOperaAll opera and light opera
9ConcertsPopAll rock and pop music concerts
10ConcertsJazzAll jazz singers and bands
11ConcertsClassicalAll 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
catidcatgroupcatnamecatdesc
11ConcertsClassicalAll symphony, concerto, and choir concerts
10ConcertsJazzAll jazz singers and bands
9ConcertsPopAll rock and pop music concerts
8ShowsOperaAll opera and light opera
7ShowsPlaysAll non-musical theatre
6ShowsMusicalsMusical theatre
5SportsMLSMajor League Soccer
4SportsNBANational Basketball Association
3SportsNFLNational Football League
2SportsNHLNational Hockey League
1SportsMLBMajor 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
catidcatgroupcatnamecatdesc
1SportsMLBMajor League Baseball
5SportsMLSMajor League Soccer
4SportsNBANational Basketball Association
3SportsNFLNational Football League
2SportsNHLNational Hockey League
6ShowsMusicalsMusical theatre
8ShowsOperaAll opera and light opera
7ShowsPlaysAll non-musical theatre
11ConcertsClassicalAll symphony, concerto, and choir concerts
10ConcertsJazzAll jazz singers and bands
9ConcertsPopAll 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
catidcatdescsports
1Major League BaseballBaseball
4National Basketball AssociationBasketball
3National Football LeagueOther Sports
2National Hockey LeagueOther Sports
5Major League SoccerOther Sports

Leave a Comment

Your email address will not be published.