select min(rate) and distinct(code)

what is wrong with my query??

select distinct ratecodeno, min (adult_rate) from twn_rate where productno=136 and database='twn_p_mgh' and (cvm_dip_id=0 or cvm_dip_id is null) group by adult_rate order by adult_rate

What are you trying to do? Describe the table with some sample data and the expected result.

Your query is fine but you need some modification in it. For example when you are using any column with group function in SELECT clause then it must be appear in GROUP BY clause. Look at your query gain and find the difference.


SELECT distinct ratecodeno, min (adult_rate)
FROM twn_rate
WHERE productno=136
AND database='twn_p_mgh'
AND (cvm_dip_id=0 or cvm_dip_id is null)
GROUP BY ratecodeno
ORDER by adult_rate

does that means i need to have adult_rate in my group by also?? I tried this, but it didn't work
SELECT distinct ratecodeno, min (adult_rate)
FROM twn_rate
WHERE productno=136
AND database='twn_p_mgh'
AND (cvm_dip_id=0 or cvm_dip_id is null)
GROUP BY ratecodeno, adult_rate
ORDER by adult_rate

I would like to select all the record sort by adult_rate from cheapest to most expensive..

for eg my data,
ratecodeno adult_rate
1 100
1 50
2 150
3 50

so in this case, my desire output would be,
1 50
3 50
2 150