Search code examples
mysqlcase-whendbvisualizer

Applying dual conditions in case when in MYSQL

(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.

Solution 
  • 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.