SQLite Distinct Keyword

  Total Views : 63
  Comments : 0
 
  Keywords : SQLite select distinct with example, SQLite distinct clause with example, SQLite distinct keyword to remove duplicates in select statement result set with example

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

SQLite Distinct Keyword

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

 

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 combination of those columns to evaluate the duplicate values.

 

In SQLite NULL values considers as duplicates so if we use DISTINCT clause with a column that has NULL values then it will keep only one row of 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 result.

 

tables-list - It may be list of 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 is having “Patel” surname.

SQLite Select Distinct on Single Column

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

 

SELECT DISTINCT last_name FROM emp_master;

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

 

last_name

----------

Patel

Jariwala

Viras

Rana

Menpara

Shah

As you know there are 2 employees having last name “Patel”. Our SQLite select distinct  statement returns unique last name of employee from 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 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 above result SQLite SELECT DISTINCT statement returns each unique combination of first_name and last_name from emp_master table.

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User