How to select distinct rows from a table and join the selected row to another table in mysql

Question

Hi,

I have a query like below to select 'sid' with highest count and its 'lid'. Now I'd like to select details of 'lid' from another table and keep the 'tot' in the result set. Is that possible?

select count(distinct sid) as tot, lid 
from wt_stats_linkclicks 
where statsid IN (1) 
GROUP BY lid order by tot DESC limit 1

Thanks!

Answer 1

If your linkidactually is lid, this might be one way to do it.

SELECT  *
FROM    dbo.liddetails ld
        INNER JOIN (
           SELECT COUNT(DISTINCT(sid) AS tot, lid  
           FROM   wt_stats_linkclicks  
           WHERE  statsid IN (1)  
           GROUP BY 
                  lid
        ) ldtot ON ldtot.lid = ld.lid