SQL Select column based on Max(other column)

Question

if I have in tblCust

c_id       Option         Numbers
10         1              2
5          1              1
10         2              1

I want to return the option where Numbers is max for c_id = 10. I've tried

 SELECT Option, MAX(Numbers) FROM tblCust WHERE c_id = 10

but no luck

Answer 1

You didn't specfiy a DBMS, so this is standard SQL

select *
from (
   select c_id, 
          option,
          max(option) over (partition by c_id) as max_opt
   from tblCust
   where c_id = 10
) t
where option = max_opt;

Answer 2

Without subquery you can sort things to get the biggest

SELECT  Option FROM tblCust
WHERE c_id = 10
ORDER BY Number DESC LIMIT 1

Answer 3

Use this:

SELECT Option, Numbers
FROM tblCust
WHERE c_id = 10
ORDER BY Numbers DESC
LIMIT 1

output:

Option  Numbers
1       2

Answer 4

I think the use of max() over() was intended to be against Numbers, not Option

select *
from (
   select c_id, 
          option,
          max(numbers) over (partition by c_id) as max_numbers
   from tblCust
   where c_id = 10
) t
where numbers = max_numbers;

If however the data is such that you have more than one record that is equal to the max(Numbers) - and you want only one result record - then use of row_number() may be a better option.

select *
from (
       select c_id, 
              option,
              row_number() over (partition by c_id order by Numbers DESC, Option DESC) as rn
       from tblCust
       where c_id = 10
    ) AS t
where rn=1
;

note the order by within the over() here, it places highest Numbers and Option first which is then given the row_number value of 1