Applying dual conditions in case when in MYSQL

Question

(question edited) My table has id,status,date,sequence

Situation: Get ids which satisfies:

• date is max, not more than today's and status is A
• if more than 1 status' with same date then get id only if it has max sequence

I am writing MYSQL in dbVisulizer.

edit: trying with query:

select id, max(date), max(sequence) from 
table
where
table.date<=now() and status='A'
order by date desc,sequence desc

It sounds like I am just asking direct question without trying anything by myself, but for this situation I am completely stuck, I tried with case when but couldn't really accomplish any good, any starting point would be appriciated.

Answer 1

select id, max(date), max(sequence) from 
table
where
table.date<=now() and status='A'
order by date desc,sequence desc
group_by id;

This should give you the desired results. Adapt table and field names to your table and fields.

Answer 2

select id from 
table
where
table.date<now() and table.status='A'
order by date desc,sequence desc
limit 1;

You should check it for mistakes by yourself.