SQL SELECT Statement with DISTINCT clause

Filed Under: SQL
SQL SELECT DISTINCT (2)

Hey, folks! In this article, we will we focusing on SQL SELECT DISTINCT statement.


What is SQL SELECT statement?

SQL Select statement is used to select and display particular data from the column of a table/database.

Syntax:

SELECT * FROM Table-name;
OR
SELECT column1, column2, ...., columnN from Table-name;

Example:

create table Info(id integer, Cost integer, city varchar(200));
insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");
Select * from Info;

Output:

id	Cost	city
1	100	Pune
2	50	Satara
3	65	Pune
4	97	Mumbai
5	12	USA

What is SQL DISTINCT Clause?

SQL DISTINCT clause helps extract the non-redundant values from the table i.e. it excludes the redundant/repeated values and displays only the unique values.

Syntax:

DISTINCT(column-name)

Example:

SELECT DISTINCT(city) from Info;

Output:

city
Pune
Satara
Mumbai
USA

Working of SQL SELECT with DISTINCT clause

SQL SELECT statement can be clubbed along with DISTINCT clause to extract and display the unique values from a particular table.

Moreover, the SELECT DISTINCT statement can be used along with different aggregation functions such as COUNT(), AVG(), etc to display the unique values according to certain predefined conditions.

Syntax:

SELECT DISTINCT(column-name)
FROM Table;

Having understood the working of SQL SELECT DISTINCT statement, let us now have a look at the implementation of the same in the below section.


Implementing SQL SELECT DISTINCT through examples

In the below example, we have clubbed SQL DISTINCT statement along with ORDER BY clause. Wherein, it displays the unique ‘city’ data in an ascending order according to the Cost values.

Select DISTINCT(city),Cost
From Info
ORDER BY Cost;

Output:

city	Cost
USA	12
Satara	50
Pune	65
Mumbai	97
Pune	100

Now, we have used COUNT(*) function along with SELECT DISTINCT statement to display the count of all the unique values of the column — ‘city’.

SELECT COUNT(*) 
FROM (
SELECT  DISTINCT city
FROM Info) as Inn;

Output:

COUNT(*)
4

Here, we have created a Table — ‘Info’ with various columns. Further, we have used SELECT DISTINCT statement along with WHERE clause to extract and display the unique values whose ‘Cost’ is equal to 100.

create table Info(id integer, Cost integer, city varchar(200));
insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 100,"Pune");
insert into Info(id, Cost,city) values(4, 100,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");
SELECT DISTINCT city,Cost
FROM Info 
WHERE Cost=100;

Output:

city	Cost
Pune	100
Mumbai	100

Conclusion

By this, we have come to the end of this topic. Feel free to comment below in case you come across any doubt.

For more such topics related to SQL, please do visit SQL JournalDev.


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages