CODEWITHSUNDEEP
sql
alextorres61
alextorres61

Reputation: 1

SQL Select column based on Max(other column)

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

Upvotes: 0

Views: 84

Answers (4)

Paul Maxwell
Paul Maxwell

Reputation: 35613

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

Upvotes: 0

Erik Kaplun
Erik Kaplun

Reputation: 38247

Use this:

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

output:

Option  Numbers
1       2

Upvotes: 0

mb14
mb14

Reputation: 22636

Without subquery you can sort things to get the biggest

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

Upvotes: 1

user330315
user330315

Reputation:

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;

Upvotes: 1

Related Questions