PostgreSQL

Postgresql order by clause

You must be familiar with storing data in any form in any operating system if you are willing to have any running applications. Whenever you desire to run any dynamic website or an application, you always need storage in the backend. You will come to know about many storage applications named “databases”. One of the commonly used free and open-source databases is “Postgresql”. It falls in the category of Database management systems, and also, a type of RDBMS means rational database management systems. It is also declared as highly stable among all other databases. The reason behind this is, it has been developed for more than 20 years as an open-source from the community and is still considered as a primary database for many web and analytical applications.

It supports both JSON that is non-rational and rational SQL, querying. It is applicable on almost all operating systems, including Windows, macOS, Linux, and OpenBSD as well. Many companies are currently using Postgresql in their products and services like Apple, Cisco, Instagram, etc.

Postgresql is an extensible database and user-friendly because it allows customization, and users are capable of defining their own data types, functional languages, etc. Custom plugins are also developed if you don’t think that the default setting will meet your requirement. Postgresql has many features, but those that discriminate it from others are Nested transactions and Asynchronous replication.

Now moving towards the working of Postgresql. Our current mode of discussion is using a query of order in Postgresql. All commands used in Postgresql are almost the same as SQL. However, we have used two approaches to demonstrate the working of order by clause in Postgresql.

  • Applying PostgreSQL order by clause via SQL shell (psql)
  • Applying PostgreSQL order by clause using pgAdmin

Applying PostgreSQL order by clause via SQL shell (psql)

After the Postgresql is successfully installed on your system, you can now open sql shell (psql). As you have defined the local hostname and database name, provide a password to proceed further. ‘Postgres’ is the default name of the database. We have also used the same in this tutorial.

Example 1
Take an example of a table. In Postgresql, table creation is very simple and defined. The table is created through a ‘create’ command, and we use an ‘insert’ key to add values to the table. This method is discussed later in the guide. Coming back to the table named ‘car’ having the attributes of the car as column names. Use the select command to fetch all records of the table.

>> Select * from car;

Now we will apply the query in which we want to display only two columns from the table.

>> Select name, color from car order by color ASC;

And the result must be in ascending order concerning the color. So all the colors in the column are ordered alphabetically.

Example 2
Consider another table named worker that contains the information about every working person. Use the select command to show the data of the table.

In this case, we want to fetch the record in descending order concerning the column fname. Whereas, two column’s record is fetched accordingly in the table.

>> select  fname,lname from worker order by fname DESC;

You can see that the result of the fname column is arranged.

Example 3
Consider the same table worker. In this example, we created a column at run time as we said that postgresql is customizable and flexible. In this created column, we will show the result. The length of the last name of the worker is calculated by using a built-in function ‘LENGTH’; the syntax of the function is;

LENGTH(column name);

This function will display the total words in the name. We will use the query to fetch two columns, lname, and the ‘len’ column. The data in the ‘len’ column is then arranged in descending order.

>> select lname, LENGTH (lname) len from worker order by len DESC;

The resultant columns are shown with the length in descending order.

Example 4
We will talk about the null values in the table. Here we will create a new table ‘sample’ by using the create query. Here the column name is defined along with the data type that is an integer value.

>> create table sample (number INT);

The table will be created now. We will add the values in the respective column. Here we have entered all the values in a single line. You may add records separately. One row is entered as null. This null will be shown as a space in the table.

>> insert into sample(number) values (1),(5),(8),(3),(null);

Use the select statement to see the values you inserted. If we want to display the column data according to that column particularly, you will notice that each value is arranged ascendingly with the null value at the end by default.

>> select number from sample order by number;

In other words, if we want to arrange data in ascending order, we don’t need to mention it. Now we want the null value to appear in the first row. For that purpose, we will use:

>> select number from sample order by number NULLS first;

Example 5
There is a different way of arranging data of the table. Consider the table worker. We want to display the ‘yor’ of the car in descending order. For this, we have used angular bracket “>”.

>> select name, model, yor from car order by yor USING>;

The result will be in ascending order if you add the opposite angular bracket after the keyword ‘USING’. The output shows the ‘yor’ in descending order.

Applying PostgreSQL order by clause using pgAdmin

Now the second approach is through the pgAdmin of Postgresql.

Launch postgresql workbench first. Now the user is asked to write the saved password that the user has already given at the configuration time.

After pressing ‘OK’ that dialogue box will be removed. An interface will be displayed that is the home dashboard of Postgresql. To apply queries, navigate to the left side of the page. Expand the server; further, expand postgresql and then databases. Right-click the name ‘postgres’ of the database. A drop-down will appear. From here, select ‘query tool’. After selecting that, you will see that interface is changed.

Execute a simple select query. For execution, go to the menu bar and select the execute/refresh button.

Now you can see the result at the bottom of the page; that is the resultant portion. The relevant image is attached below.

Here take an example of a table as described earlier ‘worker’. Display the names of the city of a worker in ascending order. Use the query below

>> select fname, lname, city from worker order by city ASC;

Execute the query; the result will be as follow.

Conclusion

You can understand the concept in two ways, as described in the article. Postgresql provides the easiest ways of applying queries of order by clause. Only you need to have configured postgresql in your system.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.