Steve Price Steve Price - 4 months ago 11
MySQL Question

Limiting the results of a mysql query to the first matches only

I have the following mySQL query to collect prices for various domain names.

SELECT tdp.extension, tp.msetupfee
FROM tblpricing tp
INNER JOIN tbldomainpricing tdp ON tdp.id = tp.relid
WHERE tp.type = 'domainregister' AND tp.msetupfee > 0


The problem is that when it outputs the data to screen it is using the last record returned.
What I want to do is limit it to only returning the first value per domain type.

I have set up a fiddle here http://sqlfiddle.com/#!9/59aeac/1 and you can see that for .com it returns 11.35 then 9.23. I only want 11.35
I tried adding LIMIT 1 but that just made it return the first domain only. What is the cleanest solution to this issue?

Answer

add GROUP BY tdp.idto the query - this should help...