SQL MAX() omitting values

Question

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  OK!

Thanks!

score 3 · Answer 1 · answered Jun 10 '11 at 11:15

3

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'

answered Jun 10 '11 at 11:15

Bohemian

412,405
93
575
722

You're seriously suggesting 3 touches on the table when a simple CASE will suffice? – gbn Jun 10 '11 at 11:19
Your will always have one row with value `null` in your first version. Postgres version works fine in SQL Server as well. – Mikael Eriksson Jun 10 '11 at 11:32

gbn · Accepted Answer · 2011-06-10T11:29:08.053

3

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

edited Jun 10 '11 at 11:29

answered Jun 10 '11 at 11:17

gbn

422,506
82
585
676

@Bohemian: why? Prove it. Edit: MySQL and SQL Server examples on SO: http://stackoverflow.com/q/5143460/27535 – gbn Jun 10 '11 at 11:20
2

elegant use of of CASE, but it won't ever return 'DEBUG' even in the second case mentioned when DEBUG would be the correct result. – Ray Jun 10 '11 at 11:25
@Ray: ah, missed that bit. Will correct. Thanks for explaining what I'd missed, unlike some folk... – gbn Jun 10 '11 at 11:27

SQL MAX() omitting values

2 Answers2