aragorn aragorn - 1 year ago 93
SQL Question

Select if text not emply or if empty then highest key value

I am looking for an SQL Query to solve the problem below.
I have the following table:

Name | Description | RowNo

A | emptyText | 1
A | emptyText | 2
A | xxx | 3
B | yyy | 1
C | emptyText | 1
C | zzz | 2
D | emptyText | 1


and I want to select the rows where the Description is not emptyText or if there is only emptyText for a Name in the Description to get only the row with the maximum RowNo.
Or if you prefer the rows that they have the maximum RowNo value for a given Name are the only once I would like to get.

For the example above I would like to get the following outcome:

Name | Description | RowNo

A | xxx | 3
B | yyy | 1
C | zzz | 2
D | emptyText | 1


Thanks for any help in advance!

Answer Source

If we assume that the last row number has the non-empty text and "EmptyText" is really NULL, then a simple aggregation will do:

select name, max(Description), max(RowNo)
from t
group by name;

If we assume that "EmptyText" is not really a string but NULL and that there is only one row to be returned per Name, then you can do:

select name, max(Description) as Description,
       coalesce( max(case when Description is not null then RowNo end),
                 max(RowNo)
               ) as RowNo
from t
group by name;

The more general answer to your question is:

select name, Description, Rowno
from t
where Description <> 'EmptyText'
union all
select name, max(Description), max(RowNo)
from t
where Description <> 'EmptyText' and
      not exists (select 1 from t t2 where t2.Name = t.Name and t2.Descrdiption <> 'EmptyText');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download