Using Dynamic Arrays to Replicate ROW_NUMBER function in SQL server

[denzo36]

Hi folks, this one has been stumping me. Apologies, I cannot download XLB2B as I'm not on my personal pc. Using Office 365.

For those familiar with T-SQL language, you will know that there is a very helpful function called ROW_NUMBER which allows you to partition data based on criteria. The most common use case for me is to identify in our transactional data, the first or last transaction that happened for every policy. My data is a list of policies and policyv ersions. So in my example below, a new 'policy version' is created for each policynumber when a new transaction is done, going up in increments of 1. So the same policynumber occurs, having mutiple policyversion going from 1 upwards. Below is just a snippet of the data

In T-SQL, ROW_NUMBER will then create a new column and label the rows based on what I specify in my partition and how I choose to order my data. Below is when I partition by policynumber and order by policyversion descending. I think then use an outer query to only select where RowNumber = 1

I'm trying to replicate the same functionality using dynamic arrays. I can use SORTBY to sort my data by policynumber and policyversion descending but I can't figure out how to then using that resultant array to filter to only take the highest policyversion. I think some combo of FILTER based on max policyversion or something along those lines.

Below is the desired result.

Any ideas?

Thanks in advance

 

[Fluff]

How about

=LET(d,SORT(A2:B46,{1,2},{1,-1}),DROP(REDUCE("",UNIQUE(TAKE(d,,1)),LAMBDA(x,y,VSTACK(x,TAKE(FILTER(d,TAKE(d,,1)=y),1)))),1))

 

[denzo36]

Thanks Fluff, it's working as expected, thanks so much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[felixstraube]

I know it's a little late. But just for fun here is another option:

Row number function sql.xlsx
	A	B	C	D	E
1	PolicyNumber	PolicyVersion		P3003694445	3
2	P3012332815	5		P3003838196	2
3	P3012838735	11		P3012332815	9
4	P3012332815	8		P3012799616	6
5	P3012332815	9		P3012838735	14
6	P3012838735	14
7	P3012799616	4
8	P3012838735	13
9	P3012799616	6
10	P3003694445	1
11	P3003694445	2
12	P3012799616	5
13	P3012838735	10
14	P3003838196	1
15	P3012838735	12
16	P3003694445	3
17	P3012332815	7
18	P3003838196	2
19	P3012332815	6
20	P3012799616	3
21
Hoja1

Cell Formulas
Range	Formula
D1:E5	D1	=LET(d, A2:B40, n, CHOOSECOLS(d,1), v, CHOOSECOLS(d,2), fn, FILTER(n,n<>""), fv, FILTER(v,v<>""), u, UNIQUE(fn), r, REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,MAX(FILTER(fv,fn=y)))))), SORT(DROP(r,1),1) )
Dynamic array formulas.

 

[Dave Patton]

A little less complex.

Complex Formulas.xlsm
	A	B	C	D	E	F
1	PolicyNumber	PolicyVersion		PolicyNumber	Min	Max
2	P3012332815	5		P3003694445	1	3
3	P3012838735	11		P3003838196	1	2
4	P3012332815	8		P3012332815	5	9
5	P3012332815	9		P3012799616	3	6
6	P3012838735	14		P3012838735	10	14
7	P3012799616	4
8	P3012838735	13
9	P3012799616	6
10	P3003694445	1
11	P3003694445	2
12	P3012799616	5
13	P3012838735	10
14	P3003838196	1
15	P3012838735	12
16	P3003694445	3
17	P3012332815	7
18	P3003838196	2
19	P3012332815	6
20	P3012799616	3
1cc

Cell Formulas
Range	Formula
D2:D6	D2	=SORT(UNIQUE(rPN))
E2:E6	E2	=MINIFS(rPV,rPN,D2)
F2:F6	F2	=MAXIFS(rPV,rPN,D2)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rPN	='1cc'!$A$2:$A$20	D2, E2:F6
rPV	='1cc'!$B$2:$B$20	E2:F6