sql - Rank rows by specified partition - Stack Overflow

admin管理员组

文章数量:1125710

This is my fiddle

I have a table like this

create table CTE1 (
    [CC Receiver] VARCHAR(10),
    Name VARCHAR(255),
    [Division] varchar(10),
    [Old Block] varchar(10),
    [Date] Date,
    Round VARCHAR(10),
    Platform VARCHAR(10),
    block_type VARCHAR(255),
    tph_type VARCHAR(255)
);

insert into CTE (
    [CC Receiver],
    Name,
    [Division],
    [Old Block],
    [Date],
    Round,
    Platform,
    block_type,
    tph_type
)
values
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '013', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '017', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '010', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '024', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '46', '013', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '46', '016', 'Bukit', 'Collection Road');

SELECT *
FROM CTE;

CC Receiver	Name	Division	Old Block	Date	Round	Platform	block_type	tph_type
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	013	Bukit	Collection Road
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	017	Bukit	Collection Road
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	010	Bukit	Collection Road
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	024	Bukit	Collection Road
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	46	013	Bukit	Collection Road
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	46	016	Bukit	Collection Road

This is my fiddle

I have a table like this

create table CTE1 (
    [CC Receiver] VARCHAR(10),
    Name VARCHAR(255),
    [Division] varchar(10),
    [Old Block] varchar(10),
    [Date] Date,
    Round VARCHAR(10),
    Platform VARCHAR(10),
    block_type VARCHAR(255),
    tph_type VARCHAR(255)
);

insert into CTE (
    [CC Receiver],
    Name,
    [Division],
    [Old Block],
    [Date],
    Round,
    Platform,
    block_type,
    tph_type
)
values
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '013', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '017', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '010', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '45', '024', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '46', '013', 'Bukit', 'Collection Road'),
('BNAB21A020', 'ESTATE TEPIAN LANGSAT', 'A', 'M28', '2024-01-09', '46', '016', 'Bukit', 'Collection Road');

SELECT *
FROM CTE;

CC Receiver	Name	Division	Old Block	Date	Round	Platform	block_type	tph_type
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	013	Bukit	Collection Road
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	017	Bukit	Collection Road
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	010	Bukit	Collection Road
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	024	Bukit	Collection Road
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	46	013	Bukit	Collection Road
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	46	016	Bukit	Collection Road

The objective is to give row_number based on the same [CC Receiver], YEAR(Date), Month(Date), Name, Division, Old Block, and order by Round.

based on the requirement, this is my query

SELECT *, 
    ROW_NUMBER() OVER (
        PARTITION BY YEAR(Date), MONTH(Date), [CC Receiver], Name, Division, [Old Block] 
        ORDER BY Round
    ) AS rotasi
FROM CTE;

and this is the result:

CC Receiver	Name	Division	Old Block	Date	Round	Platform	block_type	tph_type	rotasi
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	013	Bukit	Collection Road	1
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	017	Bukit	Collection Road	2
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	010	Bukit	Collection Road	3
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	024	Bukit	Collection Road	4
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	46	013	Bukit	Collection Road	5
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	46	016	Bukit	Collection Road	6

This is not suitable with my requirement as the code put the order based on row number including platform, and what I want is to exclude the platform.

Expected result:

CC Receiver	Name	Division	Old Block	Date	Round	Platform	block_type	tph_type	rotasi
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	013	Bukit	Collection Road	1
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	017	Bukit	Collection Road	1
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	010	Bukit	Collection Road	1
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	45	024	Bukit	Collection Road	1
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	46	013	Bukit	Collection Road	2
BNAB21A020	ESTATE TEPIAN LANGSAT	A	M28	2024-01-09	46	016	Bukit	Collection Road	2

• sql
• sql-server
• row-number

Share Improve this question Follow edited Jan 9 at 5:18 Dale K 27.1k1515 gold badges5555 silver badges8282 bronze badges asked Jan 9 at 3:51 Fachry DzakyFachry Dzaky 6155 bronze badges 0

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

You want to use DENSE_RANK() here, not ROW_NUMBER():

SELECT t.*,
    DENSE_RANK() OVER (
        PARTITION BY YEAR(Date), MONTH(Date), [CC Receiver], Name, Division, [Old Block]
        ORDER BY Round
    ) AS rotasi
FROM yourTable t;

Note that the perceived order coming from the platform column might have been by random chance. Given that the records are identical sans the platform value, your SQL database could have chosen a row number in any order it wanted.

本文标签： sqlRank rows by specified partitionStack Overflow