Mysql rank funtion with two Columns and order by third column

Question

This is the code i use in Sql Server, I want to use the same in Mysql

unfortunately Mysql 5 does not have rank function. I tried google still it did not help for partition by multiple column and order by third column

SELECT 
A.ID, A.COL1, A.COL2, A.COL3 
FROM (
SELECT
ID, COL1, COL2, COL3, ROW_NUMBER() OVER (PARTITION BY COL1, COL2 ORDER BY COL3 DESC,ID) AS RN
FROM #temp) A
WHERE A.RN=1

Can please help on it to use in mysql

SELECT * FROM MysqlTemp
CREATE TEMPORARY TABLE MysqlTemp
(
ID INT 
, Col1 VARCHAR(20)
, Col2 VARCHAR(20)
, Col3 VARCHAR(30)
) 


INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (1,'Hi','Hi','A21');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (2,'Hi','Hi','A21');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (3,'Hello','Hello');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (4,'Hello','Hello');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (5,'Hey','Hey');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (6,'Hey','Hey','B45');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (7,'Howdy','Howdy','V44');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (8,'Howdy','Howdy');

Expected values are 1 , 3 , 6 , 7

See https://stackoverflow.com/questions/3126972/with-mysql-how-can-i-generate-a-column-containing-the-record-index-in-a-table Basically you'd add a column `@curRow := @curRow + 1 AS row_number` — Ultimater, Aug 17 '18 at 03:18
Hi, the link is for adding row number per column but I want to add row number by grouping two columns and order by third column.. — Spinach, Aug 17 '18 at 03:45
If you only want the first result set in a given set, can't you just use `LIMIT 1` rather than `WHERE A.RN=1`? Show me an example of your data and I'll show you how it can be done in MySQL. Otherwise you can use multiple variables to get a row number for a given result and multiple subqueries to give all of them row numbers, so you can run logic similar to sql server — Ultimater, Aug 17 '18 at 04:29

score 0 · Accepted Answer · edited Jul 26 '19 at 11:34

I took the liberty to add some more data to make sure I'm satisfying all the test cases.

CREATE TABLE MysqlTemp
(
ID INT 
, Col1 VARCHAR(20)
, Col2 VARCHAR(20)
, Col3 VARCHAR(30)
) ;

INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (1,'Hi','Hi','A21');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (2,'Hi','Hi','A21');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (3,'Hello','Hello');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (4,'Hello','Hello');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (5,'Hey','Hey');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (6,'Hey','Hey','B45');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (7,'Howdy','Howdy','V44');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (8,'Howdy','Howdy');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (9,'Howdy','Howdy');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (10,'Howdy','Howdy','V45');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (11,'ROWDY','ROWDY','X45');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (12,'ROWDY','ROWDY');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (13,'ROWDY','ROWDY');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (14,'ROWDY','ROWDY','X44');

    select m1.*,
(select count(1) from MysqlTemp m2 
  where   coalesce(col3,'Z') <= coalesce(m1.col3,'Z')
 and (coalesce(col3,'Z') < coalesce(m1.col3,'Z') or id <= m1.id)
 and col1 = m1.col1
 and col2 = m1.col2
   ) as rnk
from MysqlTemp m1

ID  Col1    Col2    Col3    rnk
1   Hi  Hi  A21     1
2   Hi  Hi  A21     2
3   Hello   Hello   (null)  1
4   Hello   Hello   (null)  2
5   Hey     Hey     (null)  2
6   Hey     Hey     B45     1
7   Howdy   Howdy   V44     1
8   Howdy   Howdy   (null)  3
9   Howdy   Howdy   (null)  4
10  Howdy   Howdy   V45     2
11  ROWDY   ROWDY   X45     2
12  ROWDY   ROWDY   (null)  3
13  ROWDY   ROWDY   (null)  4
14  ROWDY   ROWDY   X44     1

Explanation: in mysql, one way to generate rank is using correlated subquery.

This part is executed for each record in the query

select count(1) from MysqlTemp m2 
      where   coalesce(col3,'Z') <= coalesce(m1.col3,'Z')
     and (coalesce(col3,'Z') < coalesce(m1.col3,'Z') or id <= m1.id)
     and col1 = m1.col1
     and col2 = m1.col2

This part makes sure that the join is only dealing with records matching this condition. Basically, this is the partition clause.

 and col1 = m1.col1
 and col2 = m1.col2

This part could have been simple <=, because you have nulls, the highest value you can achieve is used to compare when null.

 where   coalesce(col3,'Z') <= coalesce(m1.col3,'Z')

So far, we are trying to count all the records that are less than col3 within same partition. first record will have only 1 record that is less than or =. Second record will have 2 records less than or =. so on. that is what is counted as rank.

The below part is the second part of order by clause.

 and (coalesce(col3,'Z') < coalesce(m1.col3,'Z') or id <= m1.id)

Why an extra or condition here? because id > for second and third records, and you will miss the first records that need to be counted. So an or.

I know it will be little fuzzy. Try out the query individually each part and you will understand.

Thanks for the prompt response, but for Howdy I would expect 7 as it is minimum however it gives 10 Also can please advise why you are using coalesce , i can not understand it — Spinach, Aug 17 '18 at 15:54
In your post you wanted "ORDER BY COL3 DESC". So basically you just want minimum non null id? — Ravi, Aug 17 '18 at 16:23
Yes Ravi, exactly this is what I want... But please explain it sorry am new — Spinach, Aug 17 '18 at 16:52

Mysql rank funtion with two Columns and order by third column

1 Answers1