Infobright Blog

Within MySQL and Infobright, the rank function is sometimes requested. There are varying ways to include rank into your query, but we wanted to highlight one potential option.

Use knowledge about the query to inject how the rank operation should be considered. For example, let's assume you have two main products: 'milk' and 'cookies'. These are great products, and you want to count which sales people (ID numbers: 1, 3, 4, 5, and 19) are your best sellers of your two amazing products.

We will make a few assumptions about your rank:

1. Your Number 1 Sales Person (rank=1) is the person with the highest total sales!
2. You want to group by products. Just because someone sells a million 'milks' doesn't mean they're better than someone who sells '30' cookies.
3. Assume that salesman_ids are unique. We don't want to give double credit for our workers!
4. This is not a dense_rank function. In other words, if three employees have the same value, the resulting ranks would be 1,1,1,4. A dense rank would return a result of 1,1,1,2. We will soon be releasing the dense_rank function in an upcoming blog.

Here's how you'd compute a rank function in Infobright.

create database foo;
use foo;

create table foo.other_table (
salesman_id int,
product_name varchar(10),
total_sales int
);

insert into foo.other_table values (5, 'milk', 1000000);
insert into foo.other_table values (3, 'milk', 2000000);
insert into foo.other_table values (4, 'milk', 3000000);
insert into foo.other_table values (1, 'milk', 500);
insert into foo.other_table values (19, 'milk', 1500);
insert into foo.other_table values (5, 'cookies', 100);
insert into foo.other_table values (3, 'cookies', 20000);
insert into foo.other_table values (4, 'cookies', 30);
insert into foo.other_table values (1, 'cookies', 50);
insert into foo.other_table values (19, 'cookies', 150);

select * from (
Select foo.salesman_id, foo.product_name, foo.total_sales,
(select 1 + count(*)
from other_table zy
where zy.product_name = foo.product_name
and zy.total_sales > foo.total_sales
order by zy.product_name, zy.total_sales, zy.salesman_id) rank
from other_table as foo
) as xy order by xy.product_name, xy.rank

+-------------+--------------+-------------+------+
| salesman_id | product_name | total_sales | rank |
+-------------+--------------+-------------+------+
| 3 | cookies | 20000 | 1 |
| 19 | cookies | 150 | 2 |
| 5 | cookies | 100 | 3 |
| 1 | cookies | 50 | 4 |
| 4 | cookies | 30 | 5 |
| 4 | milk | 3000000 | 1 |
| 3 | milk | 2000000 | 2 |
| 5 | milk | 1000000 | 3 |
| 19 | milk | 1500 | 4 |
| 1 | milk | 500 | 5 |
+-------------+--------------+-------------+------+
10 rows in set (0.01 sec)


Now, what happens when we have two workers with the same sales? Let's add employee_id number 2, and let's assign him/her the same sales as others in the company.

insert into foo.other_table values (2, 'milk', 1500); -- Same as Employee #19
insert into foo.other_table values (2, 'cookies', 30); -- Same as Employee #4

+-------------+--------------+-------------+------+
| salesman_id | product_name | total_sales | rank |
+-------------+--------------+-------------+------+
| 3 | cookies | 20000 | 1 |
| 19 | cookies | 150 | 2 |
| 5 | cookies | 100 | 3 |
| 1 | cookies | 50 | 4 |
| 4 | cookies | 30 | 5 |
| 2 | cookies | 30 | 5 |
| 4 | milk | 3000000 | 1 |
| 3 | milk | 2000000 | 2 |
| 5 | milk | 1000000 | 3 |
| 19 | milk | 1500 | 4 |
| 2 | milk | 1500 | 4 |
| 1 | milk | 500 | 6 |
+-------------+--------------+-------------+------+
12 rows in set (0.02 sec)


If you have any other functions you'd like us to discuss, please contact us at (JavaScript must be enabled to view this email address).