The syntax of the
DISTINCT clause is as follows:
DISTINCT clause is used in the
SELECT statement to remove duplicate rows from a result set. The
DISTINCT clause keeps one row for each group of duplicates. You can use the
DISTINCT clause on one or more columns of a table.
SELECT DISTINCT bcolor FROM t1 ORDER BY bcolor;
PostgreSQL DISTINCT on multiple columns
SELECT DISTINCT bcolor, fcolor FROM t1 ORDER BY bcolor, fcolor;
Because we specified both
fcolor columns in the
SELECT DISTINCT clause, PostgreSQL combined the values in both
fcolor columns to evaluate the uniqueness of rows.
PostgreSQL DISTINCT ON ORDER BY example
The following statement sorts the result set by the
fcolor, and then for each group of duplicates, it keeps the first row in the returned result set.
SELECT DISTINCT ON (bcolor) bcolor, fcolor FROM t1 ORDER BY bcolor, fcolor;
Select distinct rows by using DISTINCT operator.
Filter rows by using WHERE clause.
Sort rows by using the ORDER BY clause.
Select rows based on various operators such as BETWEEN, IN and LIKE.
Group rows into groups by using GROUP BY clause
Apply condition for groups by using HAVING clause.
Join to another table by using INNER JOIN, LEFT JOIN, RIGHT JOIN clauses.
When you install a PostgreSQL instance, you will have a corresponding PostgreSQL server service.
A database is a container of other objects such as tables, views, functions, indexes, etc. You can create as many databases as you want inside a PostgreSQL server.
The table is used to store the data. You can have many tables in a database. A special feature of PostgreSQL table is inheritance. Meanings a table (child table) can inherit from another table (parent table) so when you query data from the child table, the data from parent table is also showing up.
A schema is a logical container of tables and other objects inside a database. Each PostgreSQL database may have multiple schemas. It is important to note that schema is a part of ANSI-SQL standard.
The function is a block reusable SQL code that returns a scalar value of a list of records. In PostgreSQL, functions can also return composite objects.
Casts enable you to convert one data type into another data type. Casts actually backed by functions to perform the conversion. You can also create your own casts to override the default casting provided by PostgreSQL.
PostgreSQL is free and open source software. Its source code is available under PostgreSQL license, a liberal open source license. You are free to use, modify and distribute PostgreSQL in any form.
PostgreSQL requires very minimum maintained efforts because of its stability. Therefore, if you develop applications based on PostgreSQL, the total cost of ownership is low in comparison with other database management systems
What make PostgreSQL stand out
-multi-version concurrency control (MVCC) feature
-It allows you to add custom functions developed using different programming languages such as C/C++, Java, etc.
-you can define your own data types, index types, functional languages, etc.
check PostgreSQL Version
This set of Database Questions & Answers focuses on “Join Operations – 2”
1. A_____ is a query that retrieves rows from more than one table or view:
d) All of the mentioned
2.Which product is returned in a join query have no join condition:
d) None of the mentioned
3.Which is a join condition contains an equality operator:
c) Both and b
d) None of the mentioned
4.Which operation are allowed in a join view:
d) All of the mentioned
In the join condition, you can also use other operators like <,>,<>.
Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.
- Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.
- Equi join only have equality (=) operator in the join condition.
- Equi join can be an Inner join, Left Outer join, Right Outer join
Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys.
Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another.
In other words it gives us combinations of each row of first table with all records in second table.
The inner JOIN is used to return rows from both tables that satisfy the given condition.
Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.
Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,
- Left Outer Join
- Right Outer Join
- Full Outer Join
Left Outer Join
The left outer join returns a result table with the matched data of two tables then remaining rows of the left table and null for the right table’s column.
Right Outer Join
The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table’s columns.
Full Outer Join
The full outer join returns a result table with the matched data of two table then remaining rows of both left table and then the right table.