开发者

SQL MAX() omitting values

I have a table with a column "version" It specifies software version with a string "3.2.2.0" for example It can have too the "DEBUG" version

I want to obtain the MAX value of the column, but omitting DEBUG, DEBUG only shown if is the only value

Examples:

Version
-------
3.2.0
3.2.0
DEBUG
3.2.1
3.2.2

MAX(version) 
result obtained -> DEBUG
result desired -> 3.2.2

Version
-------
DEBUG
DEBUG
DEBUG

MAX(version) 
result -> DEBUG  O开发者_C百科K!

Thanks!


This only touches the table once. It is this simple...

...
CASE
   WHEN MIN(Version) = 'DEBUG' THEN 'DEBUG'
   ELSE MAX(NULLIF(Version, 'DEBUG'))
END AS Version
...

Original, before update:

...
MAX(CASE WHEN Version = 'DEBUG' THEN '' ELSE Version END)
...


select max(version)
from my_table
where version != 'DEBUG'
union
select max(version)
from my_table
where not exists (select * from my_table where version != 'DEBUG');

Or if you're using mysql:

select ifnull(max(version), 'DEBUG')
from my_table
where version != 'DEBUG'

and for postgres:

select coalesce(max(version), 'DEBUG')
from my_table
where version != 'DEBUG'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜