MariaDB Select Unique [With 21 Amazing Examples]

In this MariaDB tutorial, we will discuss the MariaDB Select Unique keyword and look at several examples. There are lists of the topic that comes under discussion:

• MariaDB Select Unique
• MariaDB Select Distinct Count
• MariaDB Select Unique All Columns Except One
• MariaDB Select Unique As
• MariaDB Select Unique Between Two Dates
• MariaDB Select Unique Dates
• MariaDB Select Unique Exists
• MariaDB Select Unique From Select
• MariaDB Select Unique Group By
• MariaDB Select Unique JSON
• MariaDB Select Unique Key Multiple Columns
• MariaDB Select Unique Limit
• MariaDB Select Unique Last 10 Rows
• MariaDB Select Unique Last_Insert_Id()
• MariaDB Select Unique Max
• MariaDB Select Unique Min
• MariaDB Select Unique Null
• MariaDB Select Unique Order By
• MariaDB Select Unique Where Not Null
• MariaDB Select Unique Yesterday
• MariaDB Select Unique Year

MariaDB Select Unique

In this section, we will learn and understand how to use the MariaDB DISTINCT keyword to select unique rows from the table. And which will be explained with the help of an illustrated example.

In MariaDB, the DISTINCT clause is used to remove duplicate values from the SELECT statement. The syntax of the DISTINCT keyword in the MariaDB is given below:

SYNTAX:

SELECT DISTINCT EXPRESSION FROM YOUR_TABLE_NAME 
WHERE [CONDITIONS];

The syntax explanation:

• EXPRESSION: We must specify which columns or calculations you want to retrieve.
• YOUR_TABLE_NAME: The table from which we want to retrieve. And there should be at least one table in the FROM clause.
• WHERE [CONDITIONS]: In the WHERE condition, it should be met from which records to be selected.

NOTE:

• If one expression is provided in the DISTINCT clause then the query will return the value of that column.
• If more than one expression is provided in the DISTINCT clause then the query will return multiple combinations of values of that expression.
• In MariaDB, the DISTINCT clause doesn’t ignore the NULL value. So when we are using the DISTINCT clause in the query, the result set will return NULL as a distinct value.

Here is a sample example of the MariaDB DISTINCT clause by the following query:

EXAMPLE:

SELECT PATIENT_LASTNAME,DISTINCT(PATIENT_FIRSTNAME) FROM JOHNS_HOPKINS_HOSPITAL
WHERE PATIENT_ID >=10
LIMIT 10;

As we see in the above query, the SELECT statement will retrieve all records of the PATIENT_LASTNAME column from the JOHNS_HOPKINS_HOSPITAL table. The DISTINCT function, it’s used to find unique values from the PATIENT_FIRSTNAME column from the JOHNS_HOPKINS_HOSPITAL table.

In the WHERE condition, the PATIENT_ID column is used with the GREATER THAN or EQUAL operator to find the values which are greater than or equal to 10 in the table. The SELECT statement will retrieve all records from the JOHNS_HOPKINS_HOSPITAL table if the WHERE condition gets TRUE.

Suppose, the SELECT statement will not retrieve any records from the JOHNS_HOPKINS_HOSPITAL table if the WHERE condition gets FALSE. This means that the PATIENT_ID column doesn’t carry any record which carries more values than 10 in the JOHNS_HOPKINS_HOSPITAL table.

At the end of the query, the LIMIT clause is used as LIMIT 10 to retrieve all records from the JOHNS_HOPKINS_HOSPITAL table by using the SELECT statement.

We hope that you have understood the concept of the MariaDB DISTINCT clause from the table. For better understanding, we have used an example and explained it in depth.

Read: MariaDB Difference Between Two Dates

MariaDB Select Distinct Count

We will learn and understand how to use the MariaDB DISTINCT function with the COUNT function in the SELECT statement by the query. And which will be explained with the help of an illustrated example.

In MariaDB, the COUNT function is used to return a count of an expression or column_name from the query. Here is the syntax of the MariaDB DISTINCT and COUNT function by the following query:

SYNTAX:

SELECT COUNT( DISTINCT expression) FROM YOUR_TABLE_NAME 
WHERE [CONDITIONS];

Let’s see an illustrated example of the MariaDB DISTINCT and COUNT function on the table by the following query:

EXAMPLE:

SELECT COUNT(DISTINCT PATIENT_FIRSTNAME) AS TOTAL_COUNT
PATIENT_LASTNAME 
FROM JOHNS_HOPKINS_HOSPITAL 
WHERE PATIENT_ID >10;

• In this preceding query, the SELECT statement will retrieve all records of the PATIENT_LASTNAME from the JOHNS_HOPKINS_HOSPITAL table.
• The COUNT function is used on the PATIENT_FIRSTNAME column for counting the value from the JOHNS_HOPKINS_HOSPITAL table.
• The DISTINCT function is used on the PATIENT_FIRSTNAME column to find the unique value from the table.
• To shorter the function_name, we have used an ALIAS clause with the AS keyword and given the name as TOTAL_COUNT for the output column.
• In the WHERE condition, the PATIENT_ID column is used with the GREATER THAN operator to find the value which is greater than 10 in the table.
• The SELECT statement will retrieve all records from the JOHNS_HOPKINS_HOSPITAL table if the WHERE condition gets TRUE.

We hope that you have understood how to use the MariaDB COUNT function with the DISTINCT clause on the table. For better understanding, we have used an example and explained it in depth.

Read: MariaDB Select Where Not Empty

MariaDB Select Unique All Columns Except One

In this section, we will learn and understand how to use the MariaDB DISTINCT clause to remove duplicate values from all columns except one column from the table. And which will be explained with the help of an illustrated example.

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME,PATIENT_LASTNAME 
FROM JOHNS_HOPKINS_HOSPITAL 
WHERE PATIENT_ID >10
LIMIT 10;

In this preceding query, we have used the DISTINCT clause on the PATIENT_FIRSTNAME and PATIENT_LASTNAME columns which will remove duplicate values from the table. In the WHERE condition, the PATIENT_ID column is used with the GREATER THAN operator to find values that are greater than 10.

At the end of the query, we have used the LIMIT clause as LIMIT 10 to retrieve the first 10 records from the JOHNS_HOPKINS_HOSPITAL table. The SELECT statement will retrieve all records from the JOHNS_HOPKINS_HOSPITAL table if the WHERE condition gets TRUE.

We hope that you have understood how to use the MariaDB DISTINCT clause on multiple columns except one from the table. We have used an example and explained it in depth for better understanding.

Read: MariaDB Date Add Days [With 21 Useful Examples]

MariaDB Select Unique As

In this section, we will learn and understand how to use the MariaDB DISTINCT clause with the ALIAS clause on the table. And which will be explained with the help of an illustrated example.

In MariaDB, the ALIAS clause is used to create a temporary name for the columns or table_name.

• COLUMN ALIASES is used in the column heading for the result set which becomes easier to read.
• TABLE ALIASES are used to make your SQL easier to read by shortening it or when conducting a self join (ie: listing the same table more than once in the FROM clause).

SELECT DISTINCT PATIENT_LASTNAME AS UNIQUE_LASTNAME, PATIENT_FIRSTNAME
FROM JOHNS_HOPKINS_HOSPITAL
LIMIT 10;

In this preceding query, the SELECT statement is used to retrieve all records of the PATIENT_FIRSTNAME columns from the JOHNS_HOPKINS_HOSPITAL table. In the DISTINCT function, it is used to find the unique values in the PATIENT_LASTNAME columns from the JOHNS_HOPKINS_HOSPITAL table.

To shorter the function_name, we have used the ALIAS clause with the AS keyword and given the name as UNIQUE_LASTNAME for the output column_name. We have used the LIMIT clause as LIMIT 10 to retrieve all records from the JOHNS_HOPKINS_HOSPITAL table by using the SELECT statement.

In this MariaDB tutorial, we have learned how to use the MariaDB DISTINCT clause with the ALIAS clause on the table. For better understanding, we have used an example and explained it in depth.

Read: MariaDB Check Constraint + Examples

MariaDB Select Unique Between Two Dates

In this section, we will learn and understand how to use the MariaDB DISTINCT clause and BETWEEN condition for two dates in the table. And which will be explained with the help of an illustrated example.

The BETWEEN condition in MariaDB is used to extract values from a SELECT statement array. It is also used with the UPDATE, DELETE and INSERT statements.

Here’s an example of the MariaDB DISTINCT clause with the BETWEEN condition on the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME,PATIENT_LASTNAME
FROM JOHNS_HOPKINS_HOSPITAL 
WHERE PATIENT_ADMITDATE BETWEEN '2022-02-02' AND '2022-07-08' ;

As we see in the above query, the SELECT statement is used with the DISTINCT clause for two columns in the JOHNS_HOPKINS_HOSPITAL table. In the DISTINCT function, it is used to find unique values from the PATIENT_FIRSTNAME and PATIENT_LASTNAME columns from the JOHNS_HOPKINS_HOSPITAL table.

In the WHERE condition, the PATIENT_ADMITDATE is used with the BETWEEN condition for the DATE values which are within 2022-02-02 and 2022-07-08 values from the JOHNS_HOPKINS_HOSPITAL table. The SELECT statement will retrieve all records from the JOHNS_HOPKINS_HOSPITAL table if the WHERE condition gets TRUE.

If the WHERE condition gets a FALSE value then that means the query has been executed. And it will not provide any result set from the query for the output.

On the subtopic “MariaDB Select unique between two dates,” we hope you learned how to utilize the DISTINCT function with the BETWEEN condition. We’ve given an example and gone over it in detail to help you understand.

Read: MariaDB Greatest Function

MariaDB Select Unique Dates

We’ll learn and comprehend how to utilize the MariaDB DISTINCT clause with the DATE function on the table in this part, which will be demonstrated with an example.

The DATE function in MariaDB is used to return the date component value from an expression or column_name in a query. Here is an illustrated example of the MariaDB DISTINCT clause used with the DATE function on the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME,PATIENT_LASTNAME,DATE(PATIENT_ADMITDATE)
FROM JOHNS_HOPKINS_HOSPITAL 
LIMIT 10 ;

In this query, the MariaDB SELECT statement is used to retrieve all records of the PATIENT_LASTNAME column from the JOHNS_HOPKINS_HOSPITAL table. The DISTINCT function is used to find the unique value and remove duplicate values from the PATIENT_FIRSTNAME column of the table.

The DATE function, it’s used to extract the DATE portion value from the PATIENT_ADMITDATE column from the JOHNS_HOPKINS_HOSPITAL table. At the end of the query, we have used the LIMIT clause as LIMIT 10 to retrieve the first 10 records from the JOHNS_HOPKINS_HOSPITAL table by using the SELECT statement.

In the subtopic “MariaDB Select unique dates,” we hope you learned how to utilize the DISTINCT clause with the DATE function. We’ve given an example and gone over it in detail to help you understand.

Read: MariaDB Between + Examples

MariaDB Select Unique From Select

In this section, we will learn and understand how to use the SELECT statement with the subquery and the DISTINCT clause on the table by the query. And which will be explained with the help of an illustrated example.

The MariaDB SUBQUERY is a query within the query. We can create subqueries with the SQL statement. These queries reside in the FROM, WHERE, and SELECT clauses.

NOTE:

• In MariaDB, the subquery is known as INNER SELECT or INNER QUERY.
• The main query which contains a subquery is known as OUTER QUERY or OUTER SELECT.

Let’s see an illustrated example of the MariaDB SELECT statement with the subquery and the DISTINCT clause of the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME,PATIENT_ID FROM 
(SELECT * FROM johns_hopkins_hospital
ORDER BY PATIENT_LASTNAME DESC ) VARIABLE_1 
ORDER BY PATIENT_ID DESC 
LIMIT 10;

In the INNER QUERY, the SELECT statement is used to retrieve all records of the column from the JOHNS_HOPKINS_HOSPITAL table. And in the end, we have arranged all records of the PATIENT_LASTNAME column with the DESC keyword for arranging in descending order.

In the OUTER QUERY, the SELECT statement is used to retrieve all records of the PATIENT_ID column from the JOHNS_HOPKINS_HOSPITAL table. The DISTINCT function is used to extract the unique values of the PATIENT_FIRSTNAME column from the JOHNS_HOPKINS_HOSPITAL table.

In the end, the ORDER BY clause is used on the PATIENT_ID column with the DESC keyword to arrange the records in descending order.

At the end of the query, the LIMIT clause is used as LIMIT 10 to retrieve the first 10 records from the JOHNS_HOPKINS_HOSPITAL table by using the SELECT statement.

The SELECT statement will only bring records for the output only when the INNER SELECT statement turns out to be TRUE.

In this section, we have understood how to use the subquery of the SELECT statement and the DISTINCT clause on the table by the query. We have used an example and explained it in-depth for a better concept.

Read: MariaDB Drop Index

MariaDB Select Unique Group By

We’ll learn how to use the MariaDB DISTINCT clause on the GROUP BY clause on the table by the query in this part. And which will be demonstrated with an example.

The group by clause in MariaDB divides a result’s rows into categories. The aggregate functions count(), min(), max(), sum(), and avg() are frequently used with the group by function to obtain attributes of groups.

For example, the number of elements (count), the total of values (sum), the maximum element (max), the minimum element (min), and the average of elements (avg).

Let’s see an example of the MariaDB DISTINCT and GROUP BY clause in the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME, PATIENT_ID
FROM JOHNS_HOPKINS_HOSPITAL 
GROUP BY PATIENT_LASTNAME 
LIMIT 15;

In this preceding query, the SELECT statement is used to retrieve all records of the PATIENT_ID column from the JOHNS_HOPKINS_HOSPITAL table. The DISTINCT clause is used on the PATIENT_FRISTNAME column to find the unique value from the JOHNS_HOPKINS_HOSPITAL table.

And we have grouped them by the PATIENT_LASTNAME column by using the GROUP BY clause. At the end of the query, we have used the LIMIT clause as LIMIT 15 to retrieve the first 15 records from the JOHNS_HOPKINS_HOSPITAL table by using the SELECT statement.

We learned how to utilize the MariaDB DISTINCT and GROUP BY clause on the table via the query in this MariaDB tutorial. We’ve given an example and gone over it in detail to help you understand.

Read: MariaDB GROUP BY

MariaDB Select Unique JSON

We will learn and understand how to use the MariaDB DISTINCT clause on the JSON data type of the table by query. And which will be explained with the help of an illustrated example.

MariaDB’s JSON data type is a replacement for LONGTEXT, which was designed to work with MySQL’s JSON data type. Because it violates the SQL standard, MariaDB supports the JSON data format as a LONGTEXT instead, and MariaDB’s benchmarks show that efficiency is at least comparable.

Here is an example of the MariaDB DISTINCT clause on the JSON data type of the table by the following query:

EXAMPLE:

SELECT DISTINCT USER_NAME,JSON_VALUE FROM JSON_DEMO;

In this preceding query, we have used the SELECT statement to retrieve all records of the JSON_VALUE column from the JSON_DEMO table. The DISTINCT function is used on the USER_NAME column to extract the unique value from the JSON_DEMO table.

We hope that you have understood how to use the MariaDB DISTINCT clause on the JSON data type of the table. For better understanding, we have used an example and explained it in detail.

Read: MariaDB Delete From Statement

MariaDB Select Unique Key Multiple Columns

In this section, we will learn and understand how to use the MariaDB DISTINCT clause on the MULTIPLE COLUMNS of the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME, PATIENT_LASTNAME, PATIENT_ADMITDATE FROM johns_hopkins_hospital
LIMIT 10;

As we see in the above query, the SELECT statement is used with the DISTINCT function which will retrieve all records from the JOHNS_HOPKINS_HOSPITAL table.

The DISTINCT function, it’s used to extract the unique values of the PATIENT_FIRSTNAME, PATIENT_LASTNAME, and PATIENT_ADMITDATE columns from the JOHNS_HOPKINS_HOSPITAL table.

We have used the LIMIT clause as LIMIT 10 to retrieve the first 10 records of the JOHNS_HOPKINS_HOSPITAL table which is used at the end of the query.

In this MariaDB tutorial, we hope that you have understood how to use the MariaDB DISTINCT clause on the multiple columns of the table. We have used an example and explained it in depth for better understanding.

Read: MariaDB COUNT Function

MariaDB Select Unique Limit

In this section, we will learn and understand how to use the MariaDB DISTINCT and LIMIT clause on the table by the query. And which will be explained with the help of an illustrated example.

The MariaDB SELECT LIMIT statement is used to retrieve records from one or more tables in MariaDB and limit the number of records returned based on a limit value.

Here is an illustrated example of the MariaDB DISTINCT and LIMIT clauses on the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME, PATIENT_LASTNAME, PATIENT_ADMITDATE
FROM johns_hopkins_hospital
LIMIT 10;

In this preceding query, the SELECT statement is used to retrieve all records of the PATIENT_LASTNAME and PATIENT_ADMITDATE from the JOHNS_HOPKINS_HOSPITAL table. The DISTINCT function is used to extract the unique values from the JOHNS_HOPKINS_HOSPITAL table.

At the end of the query, we have used the LIMIT clause as LIMIT 10 to retrieve the first 10 records from the JOHNS_HOPKINS_HOSPITAL table by using the SELECT statement.

In this MariaDB tutorial, we have understood the concept of how to use the MariaDB DISTINCT and LIMIT clauses on the table by the query. For better understanding, we have used an example and explained it in depth.

Read: How to load files into MariaDB

MariaDB Select Unique Last 10 Rows

We will learn and understand how to use the MariaDB DISTINCT clause with the subquery of the SELECT statement and the LIMIT clause on the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME, PATIENT_ID FROM (
   SELECT * FROM JOHNS_HOPKINS_HOSPITAL ORDER BY PATIENT_ID DESC LIMIT 10
) VARIABLE_1
   ORDER BY PATIENT_ID ASC;

In the OUTER SELECT statement, the SELECT statement is used to retrieve all records of the PATIENT_ID column from the JOHNS_HOPKINS_HOSPITAL table. The DISTINCT function, it’s used to extract the unique value of the PATIENT_FIRSTNAME column of the JOHNS_HOPKINS_HOSPITAL table.

And to arrange in the PATIENT_ID column in ascending order the ORDER BY clause is used with the ASC keyword.

In the INNER SELECT statement, it will retrieve all records from the JOHNS_HOPKINS_HOSPITAL table. And in the end, the ORDER BY clause is used on the PATIENT_ID column with the DESC keyword for arranging in descending order.

At the end of the query, the LIMIT clause is used with the LIMIT 10 to retrieve the first 10 records from the JOHNS_HOPKINS_HOSPITAL table by using the SELECT statement. With the help of the subquery of the SELECT statement, it will easily bring the last 10 records from the JOHNS_HOPKINS_HOSPITAL table.

We hope that you have understood the subtopic “MariaDB Select Unique Last 10 Rows”. We have used an example and explained it in detail for better understanding.

Read: MariaDB Max Connections

MariaDB Select Unique Last_Insert_Id()

In this section, we will learn and understand how to use the MariaDB DISTINCT clause with the LAST_INSERT_ID function of the table by the query. And which will be explained with the help of an illustrated example.

The LAST_INSERT_ID function in MariaDB returns the initial AUTO_INCREMENT value set by the most recent INSERT or UPDATE statement affecting an AUTO_INCREMENT column.

Here is an example of the LAST_INSERT_ID function with the DISTINCT clause of the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME, PATIENT_LASTNAME, LAST_INSERT_ID(PATIENT_ID) 
FROM johns_hopkins_hospital;

As we see in the above query, we have used the SELECT statement to retrieve all records of the PATIENT_LASTNAME column from the JOHNS_HOPKINS_HOSPITAL table. The DISTINCT function is used to extract the unique value of the PATIENT_FIRSTNAME column from the JOHNS_HOPKINS_HOSPITAL table.

And in the LAST_INSERT_ID function, it will extract the last record of the PATIENT_ID column from the JOHNS_HOPKINS_HOSPITAL table. In the output, we’ll see that all records will come in the last_insert_id function. Because during the INSERT statement all records were recorded at the same time and no new records were inserted after that.

We have used the LIMIT clause as LIMIT 10 at the end of the query which will bring the first 10 records from the JOHNS_HOPKINS_HOSPITAL table by using the SELECT statement.

We hope that you have understood how to use the MariaDB LAST_INSERT_ID function and the DISTINCT clause on the table by the query. For better understanding, we have used an example and explained it in detail.

Read: MariaDB Rename View [Complete tutorial]

MariaDB Select Unique Max

In this section, we will learn and understand how to use the MariaDB DISTINCT clause and MAX function on the table by query. And which will be explained with the help of an illustrated example.

In MariaDB, the MAX function is used to extract the max value by expression or column_name from the query. Here is an illustrated example of the MariaDB DISTINCT clause and MAX function on the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME, PATIENT_LASTNAME, MAX(PATIENT_ADMITDATE)
FROM JOHNS_HOPKINS_HOSPITALS;

As we see in the above query, the SELECT statement is used to retrieve all records of the PATIENT_LASTNAME column from the JOHNS_HOPKINS_HOSPITAL table. In the DISTINCT clause, it is used to extract the unique values of the PATIENT_FIRSTNAME column from the JOHNS_HOPKINS_HOSPITAL table.

In the MAX function, it will extract the max value of the PATIENT_ADMITDATE column from the JOHNS_HOPKINS_HOSPITAL table by the query.

We hope that you have understood how to use the MariaDB DISTINCT and MAX functions on the table by the query. We have used an example and explained it in detail for better understanding.

Read: MariaDB Date_Format + 9 Useful Examples

MariaDB Select Unique Min

In this section, we will learn and understand how to use the MariaDB DISTINCT clause and MIN function on the table by query. And which will be explained with the help of an illustrated example.

In MariaDB, the MIN function is used to extract the min value by expression or column_name from the query. Here is an illustrated example of the MariaDB DISTINCT clause and MIN function on the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME, PATIENT_LASTNAME, MAX(PATIENT_ADMITDATE)
FROM JOHNS_HOPKINS_HOSPITALS;

As we see in the above query, the SELECT statement is used to retrieve all records of the PATIENT_LASTNAME column from the JOHNS_HOPKINS_HOSPITAL table. In the DISTINCT clause, it is used to extract the unique values of the PATIENT_FIRSTNAME column from the JOHNS_HOPKINS_HOSPITAL table.

In the MIN function, it will extract the min value of the PATIENT_ADMITDATE column from the JOHNS_HOPKINS_HOSPITAL table by the query.

We hope that you have understood how to use the MariaDB DISTINCT and MIN functions on the table by the query. We have used an example and explained it in detail for better understanding.

Read: MariaDB ENUM – Helpful Guide

MariaDB Select Unique Null

In this section, we will learn and understand how to use the MariaDB DISTINCT clause and IS NULL conditions on the table by the query. And which will be explained with the help of an illustrated example.

The MariaDB IS NULL condition is used to test for a NULL value by the SELECT statement. It is also used with the UPDATE, DELETE and INSERT statements.

Let’s see an example of the MariaDB DISTINCT clause and IS NULL condition on the table by the following query:

EXAMPLE:

SELECT DISTINCT STATE_NAME, STATE_SHORTFORM FROM states_of_usa 
WHERE STATE_POPULATION IS NULL;

• In this preceding query, the SELECT statement is used to retrieve all records of the STATE_SHORTFORM column from the STATE_OF_USA table.
• The DISTINCT function is used to extract unique values of the STATE_NAME column from the STATES_OF_USA table.
• In the WHERE condition, the STATE_POPULATION column is used with the IS NULL condition to find the NULL value from the STATES_OF_USA table.
• The SELECT statement will retrieve all records of the STATES_OF_USA table if the WHERE condition gets TRUE.
• But if the WHERE condition turns out FALSE then the query will be executed with an empty output result set.

In this MariaDB tutorial, we have understood how to use the MariaDB DISTINCT clause and IS NULL condition on the table by the query. For better understanding, we have used an example and explained it in depth.

Read: MariaDB vs Postgres – Detailed Comparison

MariaDB Select Unique Order By

We’ll learn and comprehend how to use the MariaDB DISTINCT clause in conjunction with the ORDER BY clause on the query table. And which will be explained via the use of an example.

The MariaDB ORDER BY clause is used to sort the records for the resultset. We haven’t provided the ASC or DESC modifier in the ORDER BY clause then the result set will be sorted the expression in ascending order. This is equal to ORDER BY EXPRESSION ASC.

Here is an illustrated example of the MariaDB DISTINCT and ORDER BY clause on the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME, PATIENT_ID
FROM JOHNS_HOPKINS_HOSPITAL 
ORDER BY PATIENT_LASTNAME DESC;

In this preceding query, the SELECT statement is used to retrieve all records of the PATIENT_ID column from the JOHNS_HOPKINS_HOSPITAL table. In the DISTINCT function, it is used to extract unique values from the PATIENT_FIRSTNAME column from the JOHNS_HOPKINS_HOSPITAL table.

We used the ORDER BY clause on the PATIENT_LASTNAME column from the JOHNS_HOPKINS_HOSPITAL table after the query to sort the columns in descending order using the DESC keyword.

We hope you learned how to use the MariaDB DISTINCT and ORDER BY clauses on the DATE column by the query in this section. For a better understanding, we used an example and explained it in depth.

Read: MariaDB Not Between

MariaDB Select Unique Exists

In this MariaDB tutorial, we will learn and understand how to use the EXISTS statement with the DISTINCT clause on the table by the query. And which will be explained with the help of an illustrated example.

When the MariaDB EXISTS condition is combined with a subquery, it is said to be “met” if the subquery returns at the last record. In a SELECT, INSERT, UPDATE, or DELETE statement, it can be utilized.

Here is an illustrated example of the MariaDB EXISTS statement with the DISTINCT clause on the table by the following query:

EXAMPLE:

SELECT DISTINCT SKULLCANDY_NAME FROM SKULLCANDY_USA
WHERE EXISTS (SELECT * FROM SKULLCANDY
WHERE SKULLCANDY.ID=SKULLCANDY_USA.SKULLCANDY_ID);

In the above query, the EXISTS statement is used to retrieve all records from the SKULLCANDY_USA table. The DISTINCT function is used to extract the unique value from the SKULLCANDY_NAME column from the SKULLCANDY_USA table.

In the WHERE condition, the EXISTS statement will bring at least one record from the SKULLCANDY table with matching ID and SKULLCANDY_ID columns of both tables.

We hope that you have understood how to use the MariaDB EXISTS statement with the DISTINCT clause on the table. We have used an example and explained it in depth for better understanding.

Read: MariaDB Window functions

MariaDB Select Unique Where Not Null

In this section, we will learn and understand how to use the MariaDB DISTINCT clause and IS NOT NULL condition on the table by the query. And which will be explained with the help of an illustrated example.

In MariaDB, the IS NOT NULL condition is used to test the NON-NULL values in the SELECT statement. It is also used in the UPDATE, DELETE and INSERT statements.

Here is an illustrated example of the MariaDB DISTINCT clause and IS NOT NULL condition on the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME, PATIENT_LASTNAME FROM johns_hopkins_hospital
WHERE PATIENT_ID IS NOT NULL 
LIMIT 10;

In this preceding query, the SELECT statement is used to retrieve all records of the PATIENT_LASTNAME column from the JOHNS_HOPKINS_HOSPITAL table. The DISTINCT function, it’s used to extract the unique value from the PATIENT_FIRSTNAME column from the JOHNS_HOPKINS_HOSPITAL table.

In the WHERE condition, the PATIENT_ID column is used with the IS NOT NULL condition to find the NON-NULL value from the JOHNS_HOPKINS_HOSPITAL table. At the end of the query, we have used the LIMIT clause as LIMIT 10 to retrieve the first 10 records from the JOHNS_HOPKINS_HOSPITAL table by using the SELECT statement.

We hope that you have understood how to use the MariaDB DISTINCT clause and IS NOT NULL condition on the table by the query. For better understanding, we have used an example and explained it in detail.

Read: MariaDB LIMIT + Examples

MariaDB Select Unique Yesterday

We will learn and understand how to use the MariaDB CURRENT_DATE, SUBDATE function, and DISTINCT clause on the table by the query. And which will be explained with the help of an illustrated example.

In MariaDB, the CURRENT_DATE function is used to provide the current date and time. The SUBDATE function in MariaDB returns a date after a time/date interval has been subtracted. 

Here is a sample example of the MariaDB CURRENT_DATE, SUBDATE function, and DISTINCT clause on the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME, PATIENT_LASTNAME, SUBDATE(CURRENT_DATE, INTERVAL 1 DAY) 
FROM johns_hopkins_hospital
LIMIT 10;

In this preceding query, the SELECT statement is used to retrieve all records of the PATIENT_LASTNAME column from the JOHNS_HOPKINS_HOSPITAL table. The DISTINCT function, it’s used to extract the unique value of the PATIENT_FIRSTNAME column from the JOHNS_HOPKINS_HOSPITAL table.

In the SUBDATE function, it is used to subtract 1 day on the CURRENT_DATE function with the INTERVAL keyword from the JOHNS_HOPKINS_HOSPITAL table. At the end of the query, we have used the LIMIT clause as LIMIT 10 to retrieve the first 10 records of the JOHNS_HOPKINS_HOSPITAL table by using the SELECT statement.

We hope that you have understood how to use the CURRENT_DATE, SUBDATE functions, and DISTINCT clauses on the table by the query. For better understanding, we have used an example and explained it in detail.

Read: MariaDB Update Statement with Examples

MariaDB Select Unique Year

We’ll learn and comprehend how to use the MariaDB DISTINCT clause in conjunction with the YEAR function on the query table. And which will be explained via the use of an example.

The MariaDB YEAR function is used to extract the year portion value from the expression or column_name by the query. Let’s see an example of the MariaDB DISTINCT clause and YEAR function on the table by the following query:

EXAMPLE:

SELECT DISTINCT PATIENT_FIRSTNAME,YEAR(PATIENT_ADMITDATE) AS YEAR_VALUE
FROM JOHNS_HOPKINS_HOSPITAL 
LIMIT 10 ;

As we see in the above query, the SELECT statement is used with the DISTINCT clause and YEAR function on the JOHNS_HOPKINS_HOSPITAL table. In the DISTINCT function, it is used to find unique values from the PATIENT_FIRSTNAME column from the JOHNS_HOPKINS_HOSPITAL table.

The YEAR function extracts the year part value as YYYY from the JOHNS_HOPKINS_HOSPITAL table’s PATIENT_ADMITDATE column. We’ve used the ALIAS clause with the AS keyword to shorten the YEAR function name, and we’ve named the output column name YEAR_VALUE.

We utilized the LIMIT clause as LIMIT 10 after the query to extract the first 10 records from the JOHNS_HOPKINS_HOSPITAL table using the SELECT statement.

We learned how to utilize the MariaDB DISTINCT clause and YEAR function on the table via the query in this MariaDB tutorial. We’ve given an example and gone over it in detail to help you understand.

You may also like to read the following MariaDB tutorials.

• MariaDB Timestamp + Examples
• MariaDB Add Column With Default Value
• MariaDB If Null + Examples
• MariaDB on Duplicate Key Update
• MariaDB Primary Key With Examples

In this MariaDB lesson, we covered the use of the MariaDB Select Unique statement and various sample instances related to it. There are several listings of the topics that are discussed:

• MariaDB Select Unique
• MariaDB Select Distinct Count
• MariaDB Select Unique All Columns Except One
• MariaDB Select Unique As
• MariaDB Select Unique Between Two Dates
• MariaDB Select Unique Dates
• MariaDB Select Unique Exists
• MariaDB Select Unique From Select
• MariaDB Select Unique Group By
• MariaDB Select Unique JSON
• MariaDB Select Unique Key Multiple Columns
• MariaDB Select Unique Limit
• MariaDB Select Unique Last 10 Rows
• MariaDB Select Unique Last_Insert_Id()
• MariaDB Select Unique Max
• MariaDB Select Unique Min
• MariaDB Select Unique Null
• MariaDB Select Unique Order By
• MariaDB Select Unique Where Not Null
• MariaDB Select Unique Yesterday
• MariaDB Select Unique Year