Getting MAX of a column and adding one more-CodePudding

I'm trying to make an SQL query that returns the greatest number from a column and its respective id. For more information I have two columns ID and NUMBER. Both of them have 2 entries and I want to get the highest number with the ID next to it. This is what I tried but didn't success.

SELECT ID, MAX(NUMBER) AS MAXNUMB
FROM TABLE1
GROUP BY ID, MAXNUMB;

The problem I'm experiencing is that it just shows ALL the entries and if I add a "where" expression it just shows the same (all entries [ids numbers]).

Pd.: Yes, I got what I wanted but only with one column (number) if I add another column (ID) to select it "brokes".

CodePudding user response：

Try:

SELECT 
  ID, 
  A_NUMBER
FROM TABLE1
WHERE A_NUMBER = (
  SELECT MAX(A_NUMBER)
  FROM TABLE1);

Presuming you want the IDs* of the row with the highest number (and not, instead, the highest number for each ID -- if IDs were not unique in your table, for example).

* there may be more than one ID returned if there are two or more IDs with equal maximum numbers

CodePudding user response：

you can try this

Select ID,maxNumber
From 
(
    SELECT 
    ID, 
    (Select Max(NUMBER) from Tmp where Id = t.Id) maxNumber
FROM 
    Tmp t
)T1
Group By ID,maxNumber

CodePudding user response：

The query you posted has an illegal column name (number) and is group by the alias for the max value, which is illegal and also doesn't make sense; and you can't include the unaliased max() within the group-by either. So it's likely you're actually doing something like:

select id, max(numb) as maxnumb
from table1
group by id;

which will give one row per ID, with the maximum numb (which is the new name I've made up for your numeric column) for each ID. Or as you said you get "ALL the entries" you might have group by id, numb, which would show all rows from the table (unless there are duplicate combinations).

To get the maximum numb and the corresponding id you could group by id only, order by descending maxnumb, and then return the first row only:

select id, max(numb) as maxnumb
from table1
group by id
order by maxnumb desc
fetch first 1 row only

If there are two ID with the same maxnumb then you would only get one of them - and which one is indeterminate unless you modify the order by - but in that case you might prefer to use first 1 row with ties to see them all.

You could achieve the same thing with a subquery and analytic function to generating a ranking, and have the outer query return the highest-ranking row(s):

select id, numb as maxnumb
from (
  select id, numb, dense_rank() over (order by numb desc) as rnk
  from table1
)
where rnk = 1

You could also use keep to get the same result as first 1 row only:

select max(id) keep (dense_rank last order by numb) as id, max(numb) as maxnumb
from table1

fiddle