How do I get to choose MAX value by result of column?-CodePudding

I have this simple Table about chat Item

I have my query that gets latest entered chat by chatId item as follow

  select MAX(id)
  from ChatItem
  WHERE ChatItem.CaseInvoiceId = 160670

Now I would like to get latest item with isDone = 0 as priority.

If any of result row has isDone = 0, it will get MAX(ID) with isDone = 0. If not, it will get the MAX(ID) with isDone = 1.

Is there any simple and beautiful way to make this?

CodePudding user response：

How about:

select top 1 id
from ChatItem
where ChatItem.CaseInvoiceId = 160670
order by isdone, id desc

The idea is to order it by isDone in ASC order so 0 should be first (if there is any) and then order it by id in DESC order so MAX value should be on top and then return the top row.

This was written and tested on SQL Server.

CodePudding user response：

Just add your condition with an and:

select max ("Id")
from "ChatItem"
where "CaseInvoiceId" = 160670 and "isDone" = 0;

Example:

https://www.db-fiddle.com/f/be7ujQ4zMzzYaDS8Tg5d4v/0