Posted by hoonew on 07/13/05 06:37

Then you need an ORDER BY clause, maybe:

SELECT sw.swid, title, shortdesc, version, catid, count(dl.swid),warename
FROM software sw
LEFT JOIN downloads dl ON sw.swid = dl.swid
JOIN waretypes wt ON sw.wareid = wt.wareid
WHERE liveyn = 'y'
GROUP BY
sw.swid, title, shortdesc, version, catid
ORDER BY count(dl.swid) DESC
limit 20

toedipper wrote:
> Hello,
>
> I have the folowing query.
>
> SELECT sw.swid, title, shortdesc, version, catid, count(dl.swid),warename
> FROM software sw
> LEFT JOIN downloads dl ON sw.swid = dl.swid
> JOIN waretypes wt ON sw.wareid = wt.wareid
> WHERE liveyn = 'y'
> GROUP BY
> sw.swid, title, shortdesc, version, catid
> limit 20
>
>
> It works but it just gives me the first 20 records. I select from a
> software table and then join to a downlaods table and count instances of
> a software id, each instance is a downloand.
>
> What I really want is for the 20 to show the TOP 20 downloads
> (instances. I think what I am looking for is an equivalent statement to
> the TOP in MS Sql.
>
> Any ideas?
>
>
> Thanks,
>
> td.

[Back to original message]