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;

Select Statement in SQL Shell

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;

Select Statement in SQL Shell

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;

Select Statement in SQL Shell

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

SELECT *  FROM tutorials ORDER BY id DESC;

Select Statement in SQL Shell

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;

Select Statement in SQL Shell

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;

Select Statement in SQL Shell

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

Select Statement in PgAdmin

Step 2) In the Panel on the right,

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

Select Statement in PgAdmin

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.