DISTINCT in PostgreSQL: Select, Order By & Limit (Examples)

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 guru99.

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 Select DISTINCT in PostgreSQL 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 Postgres Select Distinct id values from our table tutorials using distinct queries in PostgreSQL

SELECT DISTINCT(id) FROM tutorials;

Step 5) You can use the PostgreSQL order by 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 Unique Postgres.
• table_name: The name of an existing table that you want to query

Various clauses are:

Commands	Description
*	Fetches records for all the rows in the table
DISTINCT	Distinct in PostgreSQL help you to remove duplicates from the result.
ORDER BY	Sort Rows based on a column Default Sort Order is Ascending. Use Keyword DESC to sort in descending order
LIMIT	Limit in PostgreSQL restricts the number of records returned by the query.