Oracle - not single group func

Question

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?

Possible duplicate of [How do I limit the number of rows returned by an Oracle query after ordering?](http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering) — shA.t, Jun 14 '16 at 23:56
Possible duplicate of [SQL not a single-group group function](http://stackoverflow.com/questions/1795198/sql-not-a-single-group-group-function) — Florin Ghita, Jun 15 '16 at 05:39

score 1 · Accepted Answer · answered Jun 15 '16 at 00:09

1

SELECT MAX( name ) KEEP ( DENSE_RANK LAST ORDER BY nmb ) AS name,
       MAX( nmb ) AS nmb
FROM   blah

or

SELECT *
FROM   (
  SELECT *
  FROM   blah
  ORDER BY nmb DESC
)
WHERE ROWNUM = 1

answered Jun 15 '16 at 00:09

MT0

86,097
7
42
90

Jorge Campos · Answer 2 · 2016-06-14T23:38:12.387

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

Have you refreshed this page? – Jorge Campos Jun 14 '16 at 23:43 — Jorge Campos, Jun 14 '16 at 23:43

Oracle - not single group func

2 Answers2