ROW_NUMBER

This section contains reference documentation for the ROW_NUMBER function.

Assigns a row number to all the rows in a specified table.

Signature

ROW_NUMBER()

ROW_NUMBER examples

Order transactions by payment date

Order transactions by the payment date and assign them row numbers.

select customer_id, payment_date, amount, ROW_NUMBER() OVER(ORDER BY payment_date from payment;
customer_id
payment_date
amount
row_number

416

2023-02-14 21:21:59.996577

2.99

1

516

2023-02-14 21:23:39.996577

4.99

2

239

2023-02-14 21:29:00.996577

4.99

3

592

2023-02-14 21:41:12.996577

6.99

4

49

2023-02-14 21:44:52.996577

0.99

5

264

2023-02-14 21:44:53.996577

3.99

6

46

2023-02-14 21:45:29.996577

4.99

7

481

2023-02-14 22:03:35.996577

2.99

8

139

2023-02-14 22:11:22.996577

2.99

9

595

2023-02-14 22:16:01.996577

2.99

10

Identify the top two transactions by customer, ordered by transaction amount

WITH payment_cte as (SELECT ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC), customer_id, payment_date, amount from payment) SELECT row_number, customer_id, payment_date, amount from payment_cte WHERE row_number <= 2;
row_number
customer_id
payment_date
amount

1

1

2023-02-15 19:37:12.996577

9.99

2

1

2023-04-11 08:42:12.996577

7.99

1

2

2023-04-30 12:16:09.996577

10.99

2

2

2023-04-30 14:49:39.996577

8.99

1

3

2023-04-27 18:51:38.996577

8.99

2

3

2023-03-21 19:19:14.996577

10.99

1

4

2023-03-18 03:43:10.996577

10.99

2

4

2023-03-20 11:24:06.996577

10.99

Identify customers with the highest number of transactions

Find the number of transactions ranked for each customer. The customer with the highest number of transactions will have a rank of 1, and so on. Order records by the total transactions in descending order. In your rankings, return a unique rank value (to cover multiple customers with the same number of transactions).

SELECT customer_id, count(*), ROW_NUMBER() OVER(ORDER BY count(*) DESC, customer_id ASC) from payment GROUP BY customer_id;
customer_id
count
row_number

148

45

1

245

42

2

144

39

3

253

39

4

410

36

5

368

34

6

\

Last updated