{"id":224,"date":"2019-10-06T10:50:11","date_gmt":"2019-10-06T03:50:11","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=224"},"modified":"2020-04-11T23:24:10","modified_gmt":"2020-04-11T16:24:10","slug":"mariadb-distinct","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-distinct\/","title":{"rendered":"MariaDB Distinct"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MariaDB distinct<\/code> to select distinct rows from a table.<\/p>\n\n\n\n

Introduction to the MariaDB distinct option<\/h2>\n\n\n\n

A select<\/a><\/code> statement may return some identical rows. To explicitly specify that you want to remove duplicate rows from the result set, you use the distinct<\/code> option:<\/p>\n\n\n

select<\/span> \n    distinct<\/span> column_name\nfrom<\/span> \n    table_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the distinct<\/code> keyword appears after the select<\/code> keyword and before columns or expressions of the select list.<\/p>\n\n\n\n
The query will return only distinct values from the column_name<\/code> in the table_name<\/code>.<\/p>\n\n\n\n
The distinct<\/code> option can also be applied to multiple columns as shown in the following statement:<\/p>\n\n\n
select<\/span>\n    distinct<\/span> column1, column2,...\nfrom<\/span>\n    table_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, the query will use the combinations of values in all the columns, which appear after the distinct<\/code> option, to evaluate the distinction of rows.<\/p>\n\n\n\n
Note that if you want to select distinct values of some columns only, you use the group by<\/code> clause.<\/p>\n\n\n\n
MariaDB distinct examples<\/h2>\n\n\n\n
We’ll use the country_stats<\/code> table from the nation<\/code> sample database<\/a> for demonstration:<\/p>\n\n\n\n
\"country_stats\"<\/figure>\n\n\n\n
A) Using the MariaDB distinct with one column<\/h3>\n\n\n\n
The values in the year<\/code> column repeats for each country.<\/p>\n\n\n
select<\/span> \n    year<\/span>\nfrom<\/span> \n    country_stats\norder<\/span> by<\/span> \n    year<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement uses the distinct<\/code> option to select the distinct years from the the year<\/code> column:<\/p>\n\n\n
select<\/span> \n    distinct<\/span> year<\/span> \nfrom<\/span> \n    country_stats\norder<\/span> by<\/span>   \n    year<\/span> desc<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
MariaDB distinct with null<\/h2>\n\n\n\n
In MariaDB, the distinct<\/code> option treats null<\/code> values the same. In other words, if you use the select distinct<\/code> to query data from a column that has null<\/code> values, the distinct<\/code> option will keep only one null<\/code> value.<\/p>\n\n\n\n
See the countries<\/code> table from the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following statement uses the distinct<\/code> option to select unique national days:<\/p>\n\n\n
select<\/span> \n    distinct<\/span> national_day\nfrom<\/span> \n    countries;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
Even though, many rows have null<\/code> values in the national_day<\/code> column, the distinct option keeps only one value.<\/p>\n\n\n\n
In this tutorial, you will learn how to use the MariaDB distinct<\/code> to select distinct rows from a table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t