SQLite Distinct Keyword

Here we will learn SQLite distinct keyword with an example, SQLite distinct with a select statement and sqlite select distinct on multiple columns to get distinct records from the table with example.

SQLite Distinct Keyword

In SQLite, the DISTINCT keyword will scan the result set in the SELECT statement and eliminate any duplicate rows to make sure that the returned rows are from a proper set of the select statement.

 

The SQLite DISTINCT keyword will consider only the columns and values specified in the SELECT statement while determining if a row is duplicate or not. If we define DISTINCT for one column in SQLite select statement then the DISTINCT clause will return unique values only for that column. In case if we use DISTINCT for multiple columns in SQLite SELECT statement then DISTINCT will use a combination of those columns to evaluate the duplicate values.

 

In SQLite NULL values considers as duplicates so if we use the DISTINCT clause with a column that has NULL values then it will keep only one row of a NULL value.

Syntax of SQLite Distinct Keyword

Following is the syntax of using SQLite DISTINCT with SELECT statement.

 

SELECT DISTINCT expressions

FROM tables-list

[WHERE conditions];

The above SQLite Select Distinct syntax contains few properties those are

 

  • expressions - It may be no. of column or expression that you want as a result. 
  • tables-list - It may be the list of the table from which you want result. 
  • WHERE conditions - It is an optional. It is one or more conditions to retrieve the result.

SQLite Distinct Keyword Example

To use SQLite Distinct keyword in select statement to remove duplicate values first create emp_master table and insert some data like as shown below.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);

 

INSERT INTO emp_master

values (1,'Honey','Patel',10100,1),

(2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),

(5,'Shweta','Rana',12000,3),

(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 50000,3);

Now run the following query to check records of emp_master table.

 

sqlite> SELECT * FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

1           Honey       Patel       10100       1

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

Here you can see that there are 2 employees who are having a “Patel” surname.

SQLite Select Distinct on Single Column

Now, let’s look at the example of SQLite SELECT DISTINCT statement with a single column.

 

SELECT DISTINCT last_name FROM emp_master;

When we run above SQLite Distinct query it will return a result like as shown following.

 

last_name

----------

Patel

Jariwala

Viras

Rana

Menpara

Shah

As you know there are 2 employees having the last name “Patel”. Our SQLite select distinct statement returns the unique last name of an employee from the emp_master table.

SQLite Select Distinct on Multiple Columns

Now let's look at how you might use the SQLite DISTINCT clause to remove duplicates from more than one field in your SELECT statement.

 

SELECT DISTINCT last_name, first_name FROM emp_master;

In the above SQLite Select Distinct example, we used DISTINCT for two columns (last_name, first_name) to remove duplicate values. Now we will run and see the result of query that will be like as shown below.

 

last_name   first_name

----------  ----------

Patel       Honey

Jariwala    Shweta

Jariwala    Vinay

Viras       Jagruti

Rana        Shweta

Menpara     Sonal

Patel       Yamini

Shah        Khyati

If you observe the above result, the SQLite SELECT DISTINCT statement returns each unique combination of first_name and last_name from the emp_master table.

Previous
 
Next