[Mariadb/row_number()]데이터에 그룹별로 넘버링

 

https://mariadb.com/kb/en/row_number/

ROW_NUMBER

 

 

| 학생별로 넘버링

 

# select 데이터

 

# course별 mark 순서로 넘버링 -rank() ,  # course별 mark 순서로 넘버링 - dense_rank()

# course 별 넘버링하는 방법 - row_number()

 

| 쿼리

SELECT 
  RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS rank, 
  DENSE_RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS dense_rank, 
  ROW_NUMBER() OVER (PARTITION BY course ORDER BY mark DESC) AS row_num, 
  course, mark, name 
FROM student ORDER BY course, mark DESC;

 

 

| 해당 데이터 생성 쿼리

CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10));

INSERT INTO student VALUES 
  ('Maths', 60, 'Thulile'),
  ('Maths', 60, 'Pritha'),
  ('Maths', 70, 'Voitto'),
  ('Maths', 55, 'Chun'),
  ('Biology', 60, 'Bilal'),
   ('Biology', 70, 'Roger');