How to count all records and outputs when a condition is met? [SQL]

Question

I would like to count all clients and output the signup date for the 4th customer who has signed up. I mean, evaluate the number of clients that signed up and once the count of clients that have signed up reaches 4 to output the signup date and the id of the 4th customer

Sample table

customer_id     signup_date   
3994            2020-01-01
9393            2020-01-02
2144            2020-01-03
8373            2020-01-04

Output table

customer_id   signup_date
8373            2020-01-04

Does that applied on the first four customers only or for every four customers signed up? i.e. customer 4, customer 8 , customer 12, ... — ahmed, Aug 05 '22 at 19:23
`select * from (select *, row_number() over(order by signup_date) as rn from t) x where rn = 4` — The Impaler, Aug 05 '22 at 21:05

score 2 · Answer 1 · answered Aug 12 '22 at 17:18

2

You can use the ROW_NUMBER() window function to identify the location of each row according to a specific ordering.

In the query shown below the subquery computes the column rn by using the ROW_NUMBER() function. Then the outer query can just filter out the rows according to the value of this column:

select * from (
  select *, row_number() over(order by signup_date) as rn 
  from t
) x 
where rn = 4

answered Aug 12 '22 at 17:18

The Impaler

45,731
9
39
76

1

Good answer! Just worth mentioning `ROW_NUMBER` will only work in MySQL 8.0.0 or higher - but there are [other slightly hacky solutions](https://stackoverflow.com/questions/1895110/row-number-in-mysql) for previous versions. – Steve Chambers Aug 12 '22 at 17:31
1

@SteveChambers I have mixed opinions about that (depending on the day, I guess). On one side, MySQL 5.x is reaching end of life in 14 months from now, but on the flip side tons of people are still using it. Maybe Oracle will extends its life at some point. – The Impaler Aug 12 '22 at 17:41

score 1 · Answer 2 · answered Aug 12 '22 at 19:55

1

SELECT * FROM t
    ORDER BY signup_date
    LIMIT 1  OFFSET 3

(Works on all versions of MySQL.)

answered Aug 12 '22 at 19:55

Rick James

135,179
13
127
222

How to count all records and outputs when a condition is met? [SQL]

2 Answers2