Implementation of Rank and Dense Rank in MySQL

Question

Hi I am a Begineer in MySql and I want to Implement Rank and Dense Rank Function on MySql for the given problem statement stated below.

I have a Table called Transaction which has following Columns:

Transaction_no | Register|Adult|child 

The Input Data is as follows:
INPUT

+----------------+----------+-------+-------+
| transaction_no | register | adult | child |
+----------------+----------+-------+-------+
|           1234 |        A |     0 |     1 |
|           1234 |        A |     1 |     2 |
|           1234 |        A |     1 |     1 |
|           3456 |        B |     1 |     0 |
|           5678 |        B |     1 |     0 |
|           2468 |        C |     1 |     0 |
|           2468 |        C |     0 |     1 |
+----------------+----------+-------+-------+

My Requirement is to add another column namely rn using mySQL which will use Rank and dense rank like logic to generate the following intermediate output

INTERMEDIATE

+----------------+----------+-------+-------+----+
| transaction_no | register | adult | child | rn | 
+----------------+----------+-------+-------+----+
|           1234 |        A |     0 |     1 |  1 |
|           1234 |        A |     1 |     2 |  2 |
|           1234 |        A |     1 |     1 |  3 |
|           3456 |        B |     1 |     0 |  1 |
|           5678 |        B |     1 |     0 |  1 |
|           2468 |        C |     1 |     0 |  1 |
|           2468 |        C |     0 |     1 |  2 |
+----------------+----------+-------+-------+----+

Here the partition is done on transaction number.

The Final Query Output should contain all the rows whose rn=1 and the rn value should not displayed.
OUTPUT

+----------------+----------+-------+-------+
| transaction_no | register | adult | child |
+----------------+----------+-------+-------+
|           1234 |        A |     0 |     1 |
|           3456 |        B |     1 |     0 |
|           5678 |        B |     1 |     0 |
|           2468 |        C |     1 |     0 |
+----------------+----------+-------+-------+

Oracle Documentation for Reference : OracleDocument

I have also added SQL fiddle for Reference.SqlFiddle

Please help me on this.

Answer 1

MySQL Solution:

SELECT transaction_no, register, adult, child
  FROM (
         SELECT
                ( CASE WHEN @prev_tno != transaction_no THEN @rn:=1 
                       ELSE @rn:=(@rn+1) END ) AS rn
              , @prev_tno:=transaction_no AS transaction_no
              , register, adult, child
           FROM instructor
              , (SELECT @rn:=0, @prev_tno:=NULL) AS row_nums
       ) src
 WHERE rn = 1
 ORDER BY register, transaction_no

Note: Desired ordering can only be achieved on explicit request, hence ORDER BY is used in the query.

Answer 2

declare @t table (TransactionId int,register VARCHAR(1),Adult INT,Child INT )
insert into @t (TransactionId,register,Adult,Child)values (1234,'A',0,1),
(1234,'A',1,2),(1234,'A',1,1),(3456,'B',1,0),(5678,'B',1,0),(2468,'C',1,0),(2468,'C',0,1)
;with cte as (
select TransactionId,register,Adult,Child,ROW_NUMBER()OVER(PARTITION BY TransactionId,register  ORDER BY register ) RN from @t
)
Select TransactionId,register,Adult,Child from cte where RN = 1

Answer 3

create table TRANSACTION ( Transaction_no int, Register varchar2(2), Adult int, child int );

Insert rows in TRANSACTION table.

Sqlquery for Intermediate output:-

select TRANSACTION_NO,REGISTER,Adult,child, DENSE_RANK() over(PARTITION BY REGISTER ORDER BY Adult,child) as rnk from TRANSACTION;

Sqlquery for Final output:-

select TRANSACTION_NO,REGISTER,Adult,child from ( select TRANSACTION_NO,REGISTER,Adult,child, DENSE_RANK() over(PARTITION BY TRANSACTION_NO ORDER BY Adult,child) as rnk from TRANSACTION) where rnk=1;

I tried it on Oracle.