CODEWITHSUNDEEP
mysql
Ron van der Heijden
Ron van der Heijden

Reputation: 15080

To limit rows on different dates

I have a table with users and birth dates.

I want to show the users per day with a maximum of 4 days.

What I have:

SELECT *, DATE_FORMAT( `user_birthdate`, '%m-%d') as `user_birthday`
FROM `users`
WHERE `user_birthdate` IS NOT NULL 
ORDER BY CASE
    WHEN `user_birthday` >= DATE_FORMAT( CURRENT_TIMESTAMP , '%m-%d' ) 
    THEN `user_birthday`
    ELSE `user_birthday` < DATE_FORMAT( CURRENT_TIMESTAMP , '%m-%d' ) 
END

The result will be (today is 08 jan):

Birthdays

How can I limit the total different days (so that 15 sep and 07 jan are not received from the database)?

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you want to limit to 4 days rather than 4 rows, then you need some additional work. One method uses a subquery. A simpler method uses variables:

SELECT u.*
FROM (
    SELECT 
        u.*, 
        DATE_FORMAT( `user_birthdate`, '%m-%d') as `user_birthday`,
        (@rn := if(@bd = DATE_FORMAT( `user_birthdate`, '%m-%d'), @rn, if(@bd := DATE_FORMAT( `user_birthdate`, '%m-%d'), @rn + 1, @rn + 1) ) ) as run
    FROM `users` u CROSS JOIN (SELECT @bd := '', @rn := 0) params
    WHERE `user_birthdate` IS NOT NULL 
    ORDER BY (
        CASE 
            WHEN `user_birthday` >= DATE_FORMAT( CURRENT_TIMESTAMP , '%m-%d' ) 
            THEN `user_birthday`
            ELSE `user_birthday` < DATE_FORMAT( CURRENT_TIMESTAMP , '%m-%d' ) 
        END
    )
) u
WHERE run <= 4

Upvotes: 1

Related Questions