MariaDB Median – Complete Tutorial

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

• MariaDB Median
• MariaDB Median Group By
• MariaDB Median Average
• MariaDB Median and Max
• MariaDB Median Age
• MariaDB Median Date
• MariaDB Median Join
• MariaDB Median Limit
• MariaDB Median Like
• MariaDB Median Left Join
• MariaDB Median Min Max
• MariaDB Median Max
• MariaDB Median Min
• MariaDB Median Not Working
• MariaDB Median Name
• MariaDB Median Percentage
• MariaDB Median Partition By
• MariaDB Median Price
• MariaDB Median Rank
• MariaDB Median Year

MariaDB Median

We’ll understand and learn about the MariaDB MEDIAN function in the detail and which is explained with the help of syntax and an illustrated example.

In MariaDB, the Median function is used to return the median value from the group of values in the table. If there is a special case of the PERCENTILE_COUNT function, with the argument of 0.5 and the ORDER BY clause is the one in the MEDIAN expression.

Let’s see the syntax of the MEDIAN function by the following query:

SYNTAX:

SELECT expression, MEDIAN(argument_expression) OVER (PARTITION BY partition_exp) FROM TABLE_NAME
WHERE [CONDITION];

First, let’s have a look at the CUSTOMER_LIST table by using the SELECT statement in the following query:

SELECT * FROM CUSTOMER_LIST;

The MariaDB SELECT statement example retrieves all records from the CUSTOMER_LIST table.

Here is the illustrated example of the MEDIAN function by the following query:

EXAMPLE:

SELECT FIRST_NAME,MEDIAN(PRICE) OVER (PARTITION BY FIRST_NAME) FROM CUSTOMER_LIST
LIMIT 5;

• As we see in the preceding query, we have retrieved the FIRST_NAME and median the price of the customer based on the MEDIAN function.
• And partitioned it by the FIRST_NAME column by using the PARTITION BY clause from the CUSTOMER_LIST table.
• In the end, we have used the LIMIT clause as LIMIT 5 to get the first 5 records from the CUSTOMER_LIST table by using the SELECT statement for the resultset.

Read: MariaDB Delete From Statement

MariaDB Median Group By

We’ll learn how to utilize the MEDIAN function with the GROUP BY clause in MariaDB using the syntax and an example.

In a MariaDB SELECT statement, the MariaDB GROUP BY clause is used to collect data from numerous records and gang the output by one or more fields. Here is the illustrated example of the MEDIAN function with the GROUP BY clause by the following query:

EXAMPLE:

SELECT FIRST_NAME,MEDIAN(PRICE) OVER (PARTITION BY FIRST_NAME) FROM CUSTOMER_LIST
GROUP BY FIRST_NAME 
LIMIT 5;

As we see in the preceding query, we have retrieved the LAST_NAME and median the price of the customer based on the MEDIAN function. And partitioned it by FIRST_NAME column by using the PARTITION BY clause and also by GROUP BY clause from the CUSTOMER_LIST table.

In the end, we have used the LIMIT clause as LIMIT 5 to get the first 5 records from the CUSTOMER_LIST table by using the SELECT statement for the resultset.

Read: MariaDB Select Into + Examples

MariaDB Median Average

We’ll learn how to utilize the MEDIAN function with the AVG function in a MariaDB database in this part, which is described within an example.

In MariaDB, the AVG function return average of the expression or column of the table. Let’s see and use the AVG function with the MEDIAN function by the following query;

EXAMPLE:

SELECT *, AVG(PRICE),MEDIAN(PRICE) OVER(PARTITION BY FIRST_NAME) AS MEDIAN_PRICE_OF_FIRST_NAME
FROM CUSTOMER_LIST;

• We retrieved all records from the CUSTOMER_LIST table in the previous query.
• Using the AVG function on the PRICE column, we computed the average price of the customer’s merchandise.
• Then, using the MEDIAN function, we determined the PRICE column’s median value and partitioned it based on the FIRST_NAME column’s median value.
• We have supplied a new alias name for the MEDIAN_PRICE_OF_FIRST_NAME column in the resultset for calculating the MEDIAN function, which is done using the SELECT statement in the query.

Read: MariaDB Case Statement

MariaDB Median and Max

We’ll learn how to utilize the MEDIAN function with the MAX function in a query in this part, which is described with illustrated syntax and an example.

In MariaDB, the MAX function is used to find the greatest value in the expression or column from the query. Let’s see how the MEDIAN function and MAX function work as shown in the example below:

EXAMPLE:

SELECT *, MAX(PRICE),MEDIAN(PRICE) OVER(PARTITION BY FIRST_NAME) AS MEDIAN_PRICE_OF_FIRST_NAME
FROM CUSTOMER_LIST;

Using the SELECT statement, we were able to retrieve one whole row from the CUSTOMER_LIST table based on the MAX and MEDIAN functions of the PRICE column.

We generated the PRICE column’s median value using the MEDIAN function. And then we partitioned it using the OVER function based on the FIRST_NAME column for the MEDIAN function.

As demonstrated in the output, the MEDIAN and MAX functions returned a single result set. And for the median value, we created a new column called MEDIAN_PRICE_OF_FIRST_NAME by using the AS keyword in the SELECT statement for the CUSTOMER_LIST table’s ALIAS clause.

Read: MariaDB Update Statement

MariaDB Median Age

In this section, we will learn and understand how to use the MariaDB MEDIAN function on the AGE column and which can be explained with the help of an illustrated example.

Let’s create and insert new records into the new table as WALMART_CUSTOMER by the following query:

create table WALMART_CUSTOMER (
	id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	Age  INT,
	Customer_Date DATE,
	ip_address VARCHAR(20)
);

insert into WALMART_CUSTOMER (id, first_name, last_name, Age , Customer_Date, ip_address) 
values (2, 'Nigel', 'Hanes', 30, '2022-01-28', '217.188.140.182'),
 (3, 'Bellanca', 'Ragate', 3, '2021-04-30', '169.128.34.86'),
 (4, 'Josie', 'Steggals', 97, '2021-07-17', '156.232.167.93'),
 (5, 'Jayme', 'Caville', 80, '2022-04-08', '100.202.239.15'),
 (6, 'Alana', 'Glazzard', 78, '2021-07-16', '141.45.176.118'),
 (7, 'Moria', 'Sandbrook', 27, '2022-03-05', '110.57.29.163'),
 (8, 'Inga', 'Morrall', 30, '2021-12-11', '53.201.43.35'),
 (9, 'Wang', 'Masterton', 32, '2022-01-10', '7.159.148.41'),
(10, 'Cherri', 'Gammie', 80, '2021-07-23', '240.58.184.180'),
(11, 'Marybelle', 'Mattschas', 32, '2022-01-02', '92.105.124.223'),
 (12, 'Oliver', 'Weddeburn', 20, '2022-03-08', '69.60.206.207'),
 (13, 'Zandra', 'Duesberry', 32, '2021-07-05', '82.118.126.69'),
(14, 'Neron', 'Wickwar', 51, '2022-04-15', '252.28.64.163'),
 (15, 'Jamesy', 'Cassely', 48, '2021-12-24', '178.37.33.34');

SELECT * FROM WALMART_CUSTOMER;

In the first queries, we have created a new table called WALMART_CUSTOMER by using the SELECT statement. After that, we inserted new records into the WALMART_CUSTOMER table by using the INSERT INTO statement.

If we want to retrieve new records from the WALMART_CUSTOMER table, then we will use the SELECT statement.

Here is the illustrated example of the MEDIAN function on the AGE column by the following query given below:

EXAMPLE:

SELECT MEDIAN(AGE) OVER (PARTITION BY FIRST_NAME) AS MEDIAN_VALUE_FIRSTNAME
FROM WALMART_CUSTOMER
LIMIT 5;

• In the preceding query, the MEDIAN function is used on the AGE column to find the median value.
• And to get the median value, we have partitioned it by the FIRST_NAME column from the WALMART_CUSTOMER table by using the SELECT statement.
• To shorter the function name as MEDIAN(AGE) OVER (PARTITION BY FIRST_NAME), we have used the ALIAS clause by AS keyword and it changed the result set column name into the MEDIAN_VALUE_FIRSTNAME column in the output.
• At the end of the query, we have used the LIMIT clause as LIMIT 5 to retrieve the first 5 records from the WALMART_CUSTOMER table by using the SELECT statement.

Read: MariaDB Drop Index

MariaDB Median Date

In this section, we will understand and learn how to use the MEDIAN function on the DATE column in MariaDB. Let’s understand this with the help of an example:

EXAMPLE:

SELECT FIRST_NAME,MEDIAN(CUSTOMER_DATE) OVER (PARTITION BY FIRST_NAME) AS MEDIAN_DATE
FROM WALMART_CUSTOMER
LIMIT 5;

In this query, we have retrieved records of the FIRST_NAME and used the MEDIAN function on the CUSTOMER_DATE column to find the median value of the date. This was done the by OVER function partitioned using the FIRST_NAME column to find the median date value.

To keep the function name shorter, we have used the ALIAS clause as MEDIAN_DATE by using the AS keyword from the WALMART_CUSTOMER table by using the SELECT statement.

At the end of the above query, we have used the LIMIT clause as LIMIT 5 to retrieve the first 5 records of the FIRST_NAME column by using the MEDIAN function from the WALMART_CUSTOMER table.

Read: MariaDB Rename Index

MariaDB Median Join

In this section, we will learn how to use the JOIN clause on the MEDIAN function in MariaDB and we will explain it with the help of an illustrated example.

In MariaDB, the JOIN is used to retrieve data from multiple tables in the query. It is used whenever two or more two tables are joined in the MariaDB statement. Here are the different types of the MariaDB JOINS given below:

• MariaDB INNER JOIN ( also known as SIMPLE JOIN).
• MariaDB LEFT OUTER JOIN (also known as LEFT JOIN).
• MariaDB RIGHT OUTER JOIN (also known as RIGHT JOIN).

In the MariaDB INNER JOIN statement, it will all rows from the multiple tables where the condition is met. Let’s use the INNER JOIN statement for the MEDIAN function by the following query:

EXAMPLE:

SELECT customer_list.FIRST_NAME,MEDIAN(customer_list.PRICE) OVER (PARTITION BY CUSTOMER_LIST.LAST_NAME) AS MEDIAN_PRICE,
walmart_customer.last_name, 
MEDIAN(WALMART_CUSTOMER.AGE) OVER (PARTITION BY WALMART_CUSTOMER.first_name) AS MEDIAN_AGE
FROM customer_list
INNER JOIN walmart_customer
ON customer_list.id= walmart_customer.id
LIMIT 5;

In the preceding query, we retrieve the FIRST_NAME column from the CUSTOMER_LIST table and the LAST_NAME column from the WALMART_CUSTOMER table.

And we have also calculated the median value of the PRICE and AGE column by using the MEDIAN function which was done with the help of the OVER function.

In the OVER function, we partitioned the LAST_NAME and the FIRST_NAME column from the CUSTOMER_LIST and the WALMART_CUSTOMER table.

We also shorter the function name of the MEDIAN by using the ALIAS clause as MEDIAN_PRICE and MEDIAN_AGE by using the AS keyword.

In the INNER JOIN condition, it was met by the ID column from the CUSTOMER_LIST and WALMART_CUSTOMER table.

At the end of the query, we have used the LIMIT clause as LIMIT 5 to retrieve the first 5 records from both the tables which are CUSTOMER_LIST and WALMART_CUSTOMER table by using the SELECT statement.

Read: MariaDB Rename Table

MariaDB Median Limit

In this section, we’ll learn how to use the MEDIAN function with the LIMIT clause in a query, with illustrated syntax and an example.

The MariaDB SELECT LIMIT statement is used to get records from one or more MariaDB tables and set a limit on the number of items returned. Let’s see the illustrated example of the MEDIAN function with the LIMIT clause by the following query:

EXAMPLE:

SELECT *,MEDIAN(PRICE) OVER (PARTITION BY LAST_NAME) 
FROM CUSTOMER_LIST
LIMIT 10;

We obtained one entry in the above query and used the MEDIAN function to return the PRICE column’s median value. We partitioned it by LAST_NAME column using the OVER function and the PARTITION BY clause from the CUSTOMER_LIST table.

We used the LIMIT clause as LIMIT 10 at the end of the query to extract the top 10 records from the CUSTOMER_LIST table using the SELECT statement for the resultset.

MariaDB Median Like

We’ll learn how to utilize the MEDIAN function with the LIKE clause in MariaDB, which is described with an illustrated example.

Wildcards can be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE query with the MariaDB LIKE condition. This gives us the ability to match patterns. Let’s see the syntax of the MEDIAN function with the LIKE clause by the following query:

SYNTAX:

SELECT expression, MEDIAN(COLUMN_NAME) OVER (PARTITION BY COLUMN_NAME)
FROM TABLE_NAME
WHERE EXPRESSION [NOT] LIKE [% | _ ];

Here is the illustrated example of the MEDIAN function with the LIKE clause by the following query:

EXAMPLE:

SELECT *,MEDIAN(PRICE) OVER (PARTITION BY LAST_NAME) AS MEDIAN_VALUE
FROM CUSTOMER_LIST 
WHERE FIRST_NAME LIKE 'A%';

The SELECT statement is used in the previous query to extract one row from the CUSTOMER_LIST table.

We returned the median value from the PRICE column with the MEDIAN function, and we partitioned the LAST_NAME column with the OVER function.

After that, we modified the MEDIAN_VALUE column from the CUSTOMER_LIST table to a new alias name.

In the WHERE condition, we are trying to get the names whose names start with the alphabet A in the capital letter by using the % sign with suffix in the LIKE clause. If the condition gets TRUE, it gives a new resultset with other functions in the query.

Read: MariaDB Rename Table

MariaDB Median Left Join

Here we will learn how to utilize the MEDIAN function with the LEFT JOIN clause in a query in this part, which is described with illustrated syntax and an example.

In MariaDB, it is another type of join called MariaDB LEFT OUTER JOIN. This type of join returns all records from the left-hand table based on the ON condition and only those rows from the right-hand table where join conditions are met.

Let’s see the use of the MEDIAN function with the LEFT JOIN clause by the following query:

EXAMPLE:

SELECT customer_list.FIRST_NAME,MEDIAN(customer_list.PRICE) OVER (PARTITION BY CUSTOMER_LIST.LAST_NAME) AS MEDIAN_PRICE,
walmart_customer.last_name, 
MEDIAN(WALMART_CUSTOMER.AGE) OVER (PARTITION BY WALMART_CUSTOMER.first_name)
FROM customer_list
LEFT JOIN walmart_customer
ON customer_list.id= walmart_customer.id
LIMIT 5;

In the preceding query, we retrieve the FIRST_NAME column from the CUSTOMER_LIST table and the LAST_NAME column from the WALMART_CUSTOMER table.

And we have also calculated the median value of the PRICE and AGE column by using the MEDIAN function which was done with the help of the OVER function.

In the OVER function, we partitioned the LAST_NAME and the FIRST_NAME column from the CUSTOMER_LIST and the WALMART_CUSTOMER table.

We also shorter the function name of the MEDIAN by using the ALIAS clause as MEDIAN_PRICE and MEDIAN_AGE by using the AS keyword.

In the LEFT JOIN condition, it was met by the ID column from the CUSTOMER_LIST and WALMART_CUSTOMER table.

At the end of the query, we have used the LIMIT clause as LIMIT 5 to retrieve the first 5 records from both the tables which are CUSTOMER_LIST and WALMART_CUSTOMER table by using the SELECT statement.

Read: MariaDB ENUM – Helpful Guide

MariaDB Median Min Max

We’ll learn how to utilize the MEDIAN function with the MIN function and also with the MAX function in a query in this part, which is described with illustrated example.

The MIN function in MariaDB is used to return the lowest value from a query’s expression or column_name. The MAX function, on the other hand, returns the highest value from the expression or column_name in the query.

Let’s look at how to use the MEDIAN function in a query with the MIN and MAX functions using the following example:

EXAMPLE:

SELECT *,MAX(PRICE),MIN(PRICE),MEDIAN(PRICE) OVER (PARTITION BY LAST_NAME) AS MEDIAN_VALUE
FROM CUSTOMER_LIST;

The SELECT statement was used to extract one record from the CUSTOMER_LIST table for the output in the above query. As in the query, we used the PRICE column to construct the MAX and MIN functions for the table’s maximum and minimum values.

We used the OVER function on the PRICE column to determine the median value. And partitioned it by the LAST_NAME column for the price value of the median for the MEDIAN function.

Finally, we used the ALIAS name by utilizing the AS keyword and gave the name MEDIAN_VALUE for the result set to shorten the column from MEDIAN (PRICE) OVER (PARTITION BY LAST NAME) into a short column name.

Read: MariaDB vs Postgres – Detailed Comparison

MariaDB Median Max

We’ll learn how to utilize the MEDIAN function with the MariaDB MAX function in this part, which is described with illustrated example.

The MAX function, on the other hand, returns the highest value from the expression or column_name in the query. Let’s see the illustrated example of the MEDIAN function with the MAX function by the following query:

EXAMPLE:

SELECT *,MAX(PRICE),MEDIAN(PRICE) OVER (PARTITION BY LAST_NAME) AS MEDIAN_VALUE
FROM CUSTOMER_LIST;

• In the previous query, we used the MAX function to calculate the PRICE column’s MAX value.
• And we also used the MEDIAN technique to get the median value, which required the MEDIAN function.
• We partitioned the LAST_NAME column using the OVER function to determine the MEDIAN value of the PRICE column.
• Finally, we renamed the output result by MEDIAN_VALUE by utilizing the AS keyword for the MEDIAN(PRICE) OVER (PARTITION BY LAST_NAME) which might consume a lot of memory space in the CUSTOMER_LIST table when using the SELECT query.

Read: MariaDB If Null + Examples

MariaDB Median Min

In this section, we will learn and understand how to use the MEDIAN function with the MIN function in MariaDB. And we will also discuss an example related to it

In MariaDB, the MIN function is used to find the MIN value of the expression or column_name in the query. Let’s have a look at the MIN function with the MEDIAN function by the following query:

EXAMPLE:

SELECT *,MIN(PRICE),MEDIAN(PRICE) OVER (PARTITION BY LAST_NAME) AS MEDIAN_VALUE
FROM CUSTOMER_LIST;

• In the previous query, we used the MIN function to calculate the PRICE column’s MIN value.
• And we also used the MEDIAN technique to get the median value, which required the MEDIAN function.
• We partitioned the LAST_NAME column using the OVER function to determine the MEDIAN value of the PRICE column.
• Finally, we renamed the output result by MEDIAN_VALUE by utilizing the AS keyword for the MEDIAN(PRICE) OVER (PARTITION BY LAST_NAME) which might consume a lot of memory space in the CUSTOMER_LIST table when using the SELECT query.

Read: MariaDB Variables Tutorial

MariaDB Median Not Working

In this sub-topic, we will understand why the MEDIAN function is not working in MariaDB. Let’s see the example below:

EXAMPLE:

SELECT first_name, MEDIAN(AGE) OVER (ORDER BY LAST_NAME DESC)
FROM walmart_customer;

In this query, we have tried to use the ORDER BY clause instead of the PARTITION BY clause in the MEDIAN function which leads to a logical error as per MariaDB documentation.

ERROR CORRECTION:

SELECT first_name, MEDIAN(AGE) OVER (PARTITION BY LAST_NAME)
FROM walmart_customer
LIMIT 5;

As we see in the query, we retrieve the FIRST_NAME column and calculated the MEDIAN value of the AGE column by using the MEDIAN function.

And we partitioned the LAST_NAME column by using the OVER function from the WALMART_CUSTOMER table by using the SELECT statement.

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

Read: MariaDB Logs – Helpful Guide

MariaDB Median Name

We’ll learn how to utilize the MEDIAN function on the name, but first, we’ll need to understand and use the PERCENTILE_CONT function. Let’s look at an example to better understand this function:

EXAMPLE:

SELECT LAST_NAME, PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY AGE DESC) 
OVER (PARTITION BY first_name) AS PERCENTILE_NAME
FROM walmart_customer;

In this query, we retrieve the LAST_NAME column and use the PERCENTILE_CONT for the percentage value. And then we grouped the AGE column and arranged the AGE column in descending order by using the ORDER BY clause as ORDER BY expression DESC.

Later in the query, we also partitioned the LAST_NAME column in the group by using the OVER function. And to shorter the name of the OVER function, we have used the alias name MEDIAN_VALUE by using the AS keyword from the CUSTOMER_LIST table.

Read: MariaDB Drop Table + Examples

MariaDB Median Percentage

In this section, we will understand how to use the PERCENTILE_CNT from the MariaDB table and explained it with the help of an illustrated example.

EXAMPLE:

SELECT first_name,PERCENTILE_CONT(1) WITHIN GROUP (ORDER BY PRICE DESC ) 
OVER (PARTITION BY last_name) AS MEDIAN_VALUE
FROM customer_list
LIMIT 5;

In this query, we retrieve the FIRST_NAME column and use the PERCENTILE_CONT for the percentage value. And then we grouped the PRICE column and arranged the PRICE column in descending order by using the ORDER BY clause as ORDER BY expression DESC.

Later in the query, we also partitioned the LAST_NAME column in the group by using the OVER function. And to shorter the name of the OVER function, we have used the alias name MEDIAN_VALUE by using the AS keyword from the CUSTOMER_LIST table.

In the result set, we have used the LIMIT clause as LIMIT 5 to get the first 5 records from the CUSTOMER_LIST table by using the SELECT statement.

Read: MariaDB Foreign Key + Examples

MariaDB Median Partition By

In this sub-topic, we will learn and understand how to use the MEDIAN function with the PARTITION BY clause in MariaDB. And we will explain it with the help of syntax and an example.

In MariaDB, the PARTITION BY clause is used to partition the rows into groups in the table. It comes in handy when we need to do a computation on specific rows in a group while also using other rows from the same group.

• It is always used inside the OVER clause.
• The partition is formed by the partition clause which is also known as the WINDOW function.
• If this clause in the OVER() clause is omitted, the entire table is treated as a single partition.

Here is the illustrated example of the MEDIAN function with PARTITION BY clause by the following query:

EXAMPLE:

SELECT LAST_NAME,MEDIAN(PRICE) OVER (PARTITION BY LAST_NAME) AS MEDIAN_VALUE 
FROM CUSTOMER_LIST
LIMIT 5;

As we see in the preceding query, we have retrieved the LAST_NAME and median the price of the customer based on the MEDIAN function. And partitioned it by the LAST_NAME column by using the PARTITION BY clause from the CUSTOMER_LIST table.

We have also used the ALIAS clause to shorter the name of the function as the MEDIAN_VALUE column for the output and which is done by using the AS keyword.

In the end, we have used the LIMIT clause as LIMIT 5 to get the first 5 records from the CUSTOMER_LIST table by using the SELECT statement for the resultset.

Read: MariaDB Temporary Table + Examples

MariaDB Median Price

In the sub-topic, we will use the sample example on the PRICE column by using the MEDIAN function.

In the MariaDB MEDIAN function, we will also need the PARTITION BY clause to partition any other column for the execution of the query easily.

Let’s have a look at the quick example by the following query:

EXAMPLE:

SELECT EMAIL,MEDIAN(PRICE) OVER (PARTITION BY FIRST_NAME) 
FROM CUSTOMER_LIST
LIMIT 5;

• In this query, we have retrieved the EMAIL column and used the MEDIAN function on the PRICE column for the median value.
• And to get the median value of the PRICE column, we require the OVER function to partition the FIRST_NAME column into groups from the CUSTOMER_LIST table by using the SELECT statement.
• The ALIAS clause, on the other hand, was utilized to shorten the function name of the MEDIAN(PRICE) OVER (PARTITION BY FRST_NAME) column.
• We utilized the AS keyword in the query, which is done via the SELECT statement, to convert it to the MEDIAN_VALUE column.
• At the end of the query, we have used the LIMIT clause as LIMIT 5 to get the first 5 records of the CUSTOMER_LIST table.

Read: MariaDB Left Join – Helpful Guide

MariaDB Median Rank

In this section, we will understand and learn about the MEDIAN function with the RANK function in the query, which is explained with the help of an illustrated example.

In MariaDB, the RANK function is a window function that is used to display several rows from the table. It also requires the ORDER BY sequence of the same function with identical values receiving the same result.

Let’s see the new example of the MEDIAN function with the RANK function by the following query:

EXAMPLE:

SELECT EMAIL, MEDIAN(PRICE) OVER ( PARTITION BY LAST_NAME) AS MEDIAN_VALUE,
RANK() OVER (PARTITION BY first_name ORDER BY last_name) AS RANK
FROM customer_list 
LIMIT 5;

As we see in the above query, we have retrieved the EMAIL column and used the MEDIAN function on the PRICE column. And it requires the OVER function which will partition the LAST_NAME column for the median value of the PRICE column.

And for the RANK function, we have first partitioned the FIRST_NAME column by using the PARTITION BY clause. Then we have arranged in ascending order of the LAST_NAME column by using the ORDER BY clause.

To shorter the name of both functions, we have used the ALIAS clause by AS keyword for the new column as MEDIAN_VALUE and RANK column from the CUSTOMER_LIST table. At the end of the query, we have used the LIMIT clause as LIMIT 5 to retrieve the first 5 records from the CUSTOMER_LIST table with the help of the SELECT statement.

Read: MariaDB Rename Column + Examples

MariaDB Median Year

In this section, we will understand how to use the MEDIAN function with the YEAR function in the query and which is explained with the help of an illustrated example.

In MariaDB, the YEAR function is used to find the year value from the query. Let’s see in the example below:

EXAMPLE:

SELECT YEAR(CUSTOMER_DATE) AS YEAR , MEDIAN(AGE) OVER (PARTITION BY first_name) AS MEDIAN_AGE
FROM WALMART_CUSTOMER
LIMIT 5;

In the preceding query, we have calculated the year value from the YEAR function by using the YEAR function. And then we calculated the MEDIAN value by the AGE column which is done by the OVER function, partitioned the FIRST_NAME column.

To shorter the function name of the YEAR(CUSTOMER_DATE) and MEDIAN(AGE) OVER(PARTITION BY FIRST_NAME), we have used the ALIAS clause as YEAR.

And MEDIAN_AGE by using the AS keyword on the WALMART_CUSTOMER table in the SELECT statement.

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

In this MariaDB tutorial, we have discussed the MariaDB MEDIAN function and also discusses some sample examples related to it. There are lists of the topic that comes under discussion:

• MariaDB Median
• MariaDB Median Group By
• MariaDB Median Average
• MariaDB Median and Max
• MariaDB Median Age
• MariaDB Median Date
• MariaDB Median Join
• MariaDB Median Limit
• MariaDB Median Like
• MariaDB Median Left Join
• MariaDB Median Min Max
• MariaDB Median Max
• MariaDB Median Min
• MariaDB Median Not Working
• MariaDB Median Name
• MariaDB Median Percentage
• MariaDB Median Partition By
• MariaDB Median Price
• MariaDB Median Rank
• MariaDB Median Year