Im trying to find query each model with their most used ram configuration.
Table:
PC (code, model, speed, ram, hd, cd, price)
So far, I was able to list every model with every ram config and number of times the ram config has been used.
select model, ram, max(config)
from (select model,ram,count(ram) as config
from pc
group by model, ram)
group by model, ram
Output:
MODEL RAM MAX(CONFIG)
------- ---- -----------
1232 64 2
1232 32 2
1233 128 3
1121 128 3
1233 64 1
1260 32 1
I face problems when I try to have model listed with its most used ram.
select model, ram
from (select model, ram, count(ram) as config
from pc
group by model, ram)
group by model
having config = max(config);
Error : ORA-00979: not a GROUP BY expression
with x as
(select model,ram,count(ram) as config
from pc
group by model,ram)
, y as
(select model, max(config) as mxconfig from x group by model)
select x.model, x.ram --choose max(x.ram) or min(x.ram) in case of a tie and group by x.model
from x join y on x.model = y.model and x.config = y.mxconfig
This solution uses cte to achieve what you need. If you need to get either max or min ram when there is a tie for config, you should have one more group by on model.