deckyazmi deckyazmi - 2 years ago 92
SQL Question

Oracle - not single group func

I have table :

table blah:
| name | nmb |
| andy | 10 |
| alice| 5 |
| carol| 9 |

select name,MAX(nmb) from blah;

Not a single-group group function,

How to get output like :

| name | nmb |
| andy | 10 |

Could anyone guide in the right direction?

Answer Source

You forget the group by clause

select name, MAX(nmb) from blah group by name;

But it will not give you the result you are expecting. In fact it will return the same result as you have on your table.

table blah:
| name | nmb |
| andy | 10  |
| alice|  5  |
| carol|  9  |

That's because it will group the values by the column name since there are no repeated names it will show you all registries.

If you want to get just the name with bigger nmb you have to add an where clause and a subquery to get only one registry like this:

select name, nmb
  from (select name, MAX(nmb) as nmb
          from blah
         group by name
        order by MAX(nmb) desc) as t
 where rownum = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download