In this tutorial, you have learned how to remove duplicate rows from a result set using SQLite SELECT DISTINCT clause. Note that if you select a list of columns from a table and want to get a unique combination of some columns, you can use the GROUP BY clause. The statement returns 11 rows with one NULL value. See the following statement: SELECT DISTINCT company Now, if you apply the DISTINCT clause to the statement, it will keep only one row with a NULL value. It returns 59 rows with many NULL values. SELECT companyįROM customers Code language: SQL (Structured Query Language) ( sql ) ![]() This statement returns the names of companies of customers from the customers table. To remove duplicate the city and country, you apply the DISTINCT clause to both city and country columns as shown in the following query: SELECT DISTINCTĪs mentioned earlier, SQLite uses the combination of city and country to evaluate the duplicate. The result set contains duplicate city and country e.g., Sao Paulo in Brazil as shown in the screenshot above. SELECTĬountry Code language: SQL (Structured Query Language) ( sql ) The following statement finds cities and countries of all customers. SQLite SELECT DISTINCT on multiple columns It returns 53 rows because the DISTINCT clause has removed 6 duplicate rows. There are few duplicate rows such as Berlin London, and Mountain View To remove these duplicate rows, you use the DISTINCT clause as follows: SELECT DISTINCT city ORDER BY city Code language: SQL (Structured Query Language) ( sql ) Suppose you want to know the cities where the customers locate, you can use the SELECT statement to get data from the city column of the customers table as follows: SELECT city We will use the customers table in the sample database for demonstration. In database theory, if a column contains NULL values, it means that we do not have the information about that column of particular records or the information is not applicable.įor example, if a customer has a phone number with a NULL value, it means we don’t have information about the phone number of the customer at the time of recording customer information or the customer may not have a phone number at all. If you use the DISTINCT clause with a column that has NULL values, SQLite will keep one row of a NULL value. SQLite considers NULL values as duplicates. In case you use multiple columns, SQLite uses the combination of values in these columns to evaluate the duplicate. If you use one column, SQLite uses values in that column to evaluate the duplicate. Second, you place a column or a list of columns after the DISTINCT keyword.The main reasons that differentiate each of these methods are: The ease of applying them. ![]() First, the DISTINCT clause must appear immediately after the SELECT keyword. In SQLite, there are multiple ways to avoid duplicate entries.The following statement illustrates the syntax of the DISTINCT clause: SELECT DISTINCT select_listįROM table Code language: SQL (Structured Query Language) ( sql ) ![]() The DISTINCT clause allows you to remove the duplicate rows in the result set. The DISTINCT clause is an optional clause of the SELECT statement. This seems simple enough: I want to duplicate a row in a SQLite table: INSERT INTO table SELECT FROM table WHERE rowId5 If there were no explicit unique column declarations, the statement would work, but the tables first column is declared rowID INTEGER NOT NULL PRIMARY KEY. Introduction to SQLite SELECT DISTINCT clause This will produce the following result, where there is no duplicate entry.Summary: in this tutorial, you will learn how to use the SQLite SELECT DISTINCT clause to remove duplicate rows in the result set. Sqlite> SELECT DISTINCT name FROM COMPANY Now, let us use DISTINCT keyword with the above SELECT query and see the result. SELECT DISTINCT column1, lumnNĬonsider COMPANY table with the following records.įirst, let us see how the following SELECT query returns duplicate salary records. Syntaxįollowing is the basic syntax of DISTINCT keyword to eliminate duplicate records. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records. There may be a situation when you have multiple duplicate records in a table. SQLite DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only the unique records.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |