SQL Server Partition By over Two Columns in Row_Number function

3npbholx 于 2023-08-02 发布在其他

关注(0)|答案(3)|浏览(112)

I am trying to RANK the records using the following query:

SELECT 
ROW_NUMBER() over (partition by 
TW.EMPL_ID,TW.HR_DEPT_ID,TW.Transfer_Startdate 
order by TW.EMPL_ID,TW.Effective_Bdate) RN,
TW.EMPL_ID,TW.HR_DEPT_ID,TW.Transfer_Startdate,Effective_BDate from 
TT_EMPLOYEE_WORKDAY TW
where TW.HR_DOMAIN_CODE = 'SGP'

However the resultant Row_Number computed column only displays partition for the first column. Ideally I expected to have the same value for Row_Number where the partition by column data is identical.

Any clue where I might be going wrong?

USING RANK or DENSE RANK isn't an option as I want to identify all such rows for multiple employee where EMPL_ID, HR_DEPT_ID and Transfer_StartDate are same (RN=1)

Sample data:
RN  AON_EMPL_ID   HR_DEPT_ID    Transfer_Startdate  Effective_BDate
1   0100690       69895             01/01/2017       2017-01-01
2   0100690       69895             01/01/2017       2017-01-03
3   0100690       69895             01/01/2017       2017-01-04

sql-server

来源：https://stackoverflow.com/questions/43494926/partition-by-over-two-columns-in-row-number-function

3条答案

按热度按时间

wmomyfyw1#

expanding sample data to:

create table t (
    aon_empl_id varchar(16)
  , hr_dept_id varchar(16)
  , Transfer_Startdate date
  , Effective_bdate date
);
insert into t values 
 ('0100690','69895','01/01/2017','2017-01-01')
,('0100690','69895','01/01/2017','2017-01-03')
,('0100690','69895','01/01/2017','2017-01-04')
,('0200700','69895','01/01/2016','2016-01-01')
,('0200700','69895','01/01/2016','2016-01-03')
,('0200700','69896','01/01/2017','2017-01-04')
,('0200700','69896','01/01/2017','2017-01-04');

using top with ties

select top 1 with ties
    aon_empl_id
  , hr_dept_id
  , Transfer_Startdate = convert(char(10),Transfer_Startdate,120)
  , Effective_bdate    = convert(char(10),Effective_bdate,120)
from t
order by row_number() over (
      partition by aon_empl_id, hr_dept_id, Transfer_Startdate 
      order by Effective_bdate
      )

rextester demo: http://rextester.com/KOIZ42069

returns:

+-------------+------------+--------------------+-----------------+
| aon_empl_id | hr_dept_id | Transfer_Startdate | Effective_bdate |
+-------------+------------+--------------------+-----------------+
|     0100690 |      69895 | 2017-01-01         | 2017-01-01      |
|     0200700 |      69895 | 2016-01-01         | 2016-01-01      |
|     0200700 |      69896 | 2017-01-01         | 2017-01-04      |
+-------------+------------+--------------------+-----------------+

Alternative using a common table expression with row_number() :

;with cte as (
select
    rn = row_number() over (
      partition by aon_empl_id, hr_dept_id, Transfer_Startdate 
      order by Effective_bdate
    )
  , aon_empl_id
  , hr_dept_id
  , Transfer_Startdate = convert(char(10),Transfer_Startdate,120)
  , Effective_bdate    = convert(char(10),Effective_bdate,120)
from t tw
)

select *
from cte
where rn = 1

returns:

+----+-------------+------------+--------------------+-----------------+
| rn | aon_empl_id | hr_dept_id | Transfer_Startdate | Effective_bdate |
+----+-------------+------------+--------------------+-----------------+
|  1 |     0100690 |      69895 | 2017-01-01         | 2017-01-01      |
|  1 |     0200700 |      69895 | 2016-01-01         | 2016-01-01      |
|  1 |     0200700 |      69896 | 2017-01-01         | 2017-01-04      |
+----+-------------+------------+--------------------+-----------------+

赞(0）回复(0）举报 2023-08-02

dxxyhpgq2#

SELECT 
RANK() over (partition by   --or DENSE_RANK()
TW.EMPL_ID,TW.HR_DEPT_ID,TW.Transfer_Startdate 
order by TW.EMPL_ID,TW.Effective_Bdate) RN,
TW.EMPL_ID,TW.HR_DEPT_ID,TW.Transfer_Startdate,Effective_BDate from 
TT_EMPLOYEE_WORKDAY TW
where TW.HR_DOMAIN_CODE = 'SGP'

UPDATE

SELECT 
RANK() over (partition by   --or DENSE_RANK()
TW.EMPL_ID,TW.HR_DEPT_ID,TW.Transfer_Startdate 
order by TW.EMPL_ID) RN,
TW.EMPL_ID,TW.HR_DEPT_ID,TW.Transfer_Startdate,Effective_BDate from 
TT_EMPLOYEE_WORKDAY TW
where TW.HR_DOMAIN_CODE = 'SGP'
Order by RN,TW.Effective_Bdate

赞(0）回复(0）举报 2023-08-02

rkkpypqq3#

This bit of code appears to be working:

SELECT 
dense_rank() over (partition by AON_EMPL_ID 
order by AON_EMPL_ID,HR_DEPT_ID,Transfer_StartDate) RN,
TW.AON_EMPL_ID,TW.HR_DEPT_ID,TW.Transfer_Startdate,Effective_BDate from 
TT_AON_EMPLOYEE_WORKDAY TW
where TW.HR_DOMAIN_CODE = 'SGP'

Apparently, I just need to partition by AON_EMPL_ID and everything else should go to Order By clause.

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Partition By over Two Columns in Row_Number function

3条答案

相关问题

热门标签

最新问答