Find Nth Highest values -- MySQL

Find N^th Highest values in MySQL.

To explain the N^th highest value I have created a product table with dummy data to simulate the scenario.

Table structure

product_id *	product_name *	product_cost
1	P1	1
3	P3	1
5	P5	2
6	P6	3
7	P7	7
10	P10	7
8	P8	8
9	P9	8
11	P11	11
12	P12	11
13	P13	12
14	P14	20
2	P2	25
4	P4	25
15	P15	27

SQL queries to create product table.

CREATE TABLE `product` (

  `product_id` int(11) NOT NULL,

  `product_name` varchar(100) NOT NULL,

  `product_cost` decimal(9,2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert dummy records

insert into product

values (1,'P1',1),(2,'P2',25),(3,'P3',1),(4,'P4',25),(5,'P5',2),

(6,'P6',3),(7,'P7',7),(8,'P8',8),(9,'P9',8),(10,'P10',7),

(11,'P11',11),(12,'P12',11),(13,'P13',12),(14,'P14',20),(15,'P15',27);

All RDBM database support the Min and Max aggregation functions which returns the Minimum and maximum value for the respective columns.

Find the highest purchase cost from the product table

select max(p.product_cost) from product as p

Output

max(p.product_cost)

27

Find the lowest purchase cost from the product table

select min(p.product_cost) from product as p

Output

min(p.product_cost)

1

Find the product having highest purchase cost from the product table

Wrong solution

The below queries gives the wrong output

select p.product_id,p.product_name,max(p.product_cost) from product as p

Output

product_id *	product_name *	max(p.product_cost)
1	P1	27

Actual expected output is

product_id	product_name	max(p.product_cost)
15	P15	27

Correct solution

select p.product_id,p.product_name,max(p.product_cost) from product as p where p.product_cost= (select max(product_cost)from product )

Find distinct purchase cost from product table

select distinct(product_cost) from product order by 1

Output

product_cost
1
2
3
7
8
11
12
20
25
27

According to above result we categories highest and lowest value as below

product_cost	Highest  Value	Lowest Value
1	10	1
2	9	2
3	8	3
7	7	4
8	6	5
11	5	6
12	4	7
20	3	8
25	2	9
27	1	10

The 1^st highest value is 27, 2^nd highest 25, 3rd highest 20 ….

Hence we can describe as nth highest value is X. if X = 1 then highest value is 27 and if x=10 then highest value is 1.

Let’s find list of all product having N^th highest purchase cost.

select p.product_id,p.product_name,p.product_cost from product as p  where (N^th - 1) = (select count(distinct(p1.product_cost))from product as p1 where p.product_cost <p1.product_cost );

Now place n=1 means list of all product having highest purchase cost

Output

product_id *	product_name *	product_cost
15	P15	27

Now place n=4 means list of all product having 4^th highest purchase cost

product_id *	product_name *	product_cost
13	P13	12

Now place n=6 means list of all product having 6^th highest purchase cost

product_id *	product_name *	product_cost
8	P8	8
9	P9	8

Let’s find list of all product having N^th lowest purchase cost.

select p.product_id,p.product_name,p.product_cost from product as p  where (N^th - 1) = (select count(distinct(p1.product_cost))from product as p1 where p.product_cost > p1.product_cost );

Now place n=1 means list of all product having lowest purchase cost

product_id *	product_name *	product_cost
1	P1	1
3	P3	1

ROW_NUMBER generation in MySQL

SQL server support Row_Number() function by which we can obtain the row number along with the resulting query. Unfortunately MySQL does not provided such a utility function but there is other ways around to obtain the result.

select * from (

SELECT  @rownum:=@rownum+1 as row_number ,product_id, product_name, product_cost

FROM (SELECT * FROM product p ORDER BY product_cost DESC ) my_row_number, (SELECT @rownum:=0) r ) my_product

Output

row_number	product_id *	product_name *	product_cost
1	15	P15	27
2	2	P2	25
3	4	P4	25
4	14	P14	20
5	13	P13	12
6	12	P12	11
7	11	P11	11
8	9	P9	8
9	8	P8	8
10	10	P10	7
11	7	P7	7
12	6	P6	3
13	5	P5	2
14	3	P3	1
15	1	P1	1