Mysql select query with ordering issue

Question

I want to display one paid status(1) column and then 3 unpaid(0) status column respectively.

Paid status is taken based on Expirydate, the date greater than now is considered paid status and date which is less than expiry date is considered unpaid status. Output can be based on Entry date.

My Table:

Id	Expirydate	Entrydate
1	2022-12-10	2022-10-11
2	2022-12-09	2022-09-01
3	2022-10-10	2022-10-18
4	0000-00-00	2022-10-17
5	2022-09-08	2022-10-01
6	0000-00-00	2022-10-15
7	0000-00-00	2022-09-09
8	2022-11-30	2022-09-10

Output would be:

Id	Expirydate	Entrydate	Status
1	2022-12-10	2022-10-11	1
3	2022-10-10	2022-10-18	0
4	0000-00-00	2022-10-17	0
5	2022-09-08	2022-10-01	0
2	2022-12-09	2022-09-01	1
6	0000-00-00	2022-10-15	0
7	0000-00-00	2022-09-09	0
8	2022-11-30	2022-09-10	1

NOTE: Considering now() as 2022-10-18. Also last there was only 2 unpaid status so the rest paid status would be shown.

1st attempt to calculate status:

SELECT table.* , CASE WHEN `expirydate` >= NOW() THEN "1" ELSE "0" END AS paidstatus 
 FROM table ORDER BY paidstatus DESC

Thanks.

Answer 1

You can do this to solve your problem.

SELECT *, ( table.Expirydate > NOW() ) as `Paid_Status` FROM table ORDER BY `Paid_Status` DESC;