PostgreSQL Select: Distinct, Order By, Limit

You can retrieve data from the table using a SELECT statement.

Syntax:

SELECT [column names] FROM [table_name]

Here,

• column names: Name of the columns whose value you want to retrieve
• FROM: The FROM clause defines one or more source tables for the SELECT.
• table_name: The name of an existing table that you want to query

PostgreSQL Select Statement in SQL Shell

Step 1) We have a table "tutorials" with 2 columns "id" and "tutorial_name". Lets query it. Use the following query to list data in the table

SELECT id,tutorial_name FROM tutorials;

NOTE: Use the command \c to connect to the database that contains the table you want to query. In our case, we are connected to database gtupapers.

Step 2) If you want to view all the columns in a particular table, we can use the asterisk (*) wildcard character. This means it checks every possibility and, as a result, It will return every column.

SELECT *  FROM tutorials;

It displays all the records of the tutorials table.

Step 3) You can use the ORDER clause to sort data in a table based on a particular column. The ORDER clause organizes data in A to Z order.

SELECT *  FROM tutorials ORDER BY id;

You can sort from Z to A using "DESC" after the "ORDER BY" statement.

SELECT *  FROM tutorials ORDER BY id DESC; 

Step 4) The DISTINCT clause can be used to remove duplicate rows from the result. It keeps one row for each group of duplicates.

Syntax: 
SELECT DISTINCT column_1 FROM table_name;

Lets query Distinct id values from our table tutorials

SELECT DISTINCT(id) FROM tutorials; 

Step 5) You can use the LIMIT clause to restrict the number of records returned by the SELECT query

SELECT *  FROM tutorials LIMIT 4; 

PostgreSQL Select Statement in PgAdmin

Step 1) In the Object Tree

1. Right Click on the Table
2. Select Scripts
3. Click on SELECT SCRIPT

Step 2) In the Panel on the right,

1. Edit the SELECT query if required
2. Click the Lightning Icon
3. Observe the Output

Cheat Sheet

SELECT [column names] FROM [table_name] [clause] 

Here, are the various parameters

• column names: Name of the columns whose value you want to retrieve
• FROM: The FROM clause defines one or more source tables for the SELECT.
• table_name: The name of an existing table that you want to query

Various clauses are: