Posted by Chris Hope on 07/13/05 01:56

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?

SELECT TOP in sql server works the same way; unless you specify which
order the results will appear in they are essentially random in either
database system (although IIRC they are typically ordered by the order
of the group by clause if used). You need to use ORDER BY to order the
records in the order you want.

This /should/ work:

SELECT sw.swid, title, shortdesc, version, catid,
count(dl.swid) AS somealias,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
somealias DESC
limit 20

Note that "warename" is not included in your GROUP BY clause. While this
may not cause an error in MySQL it will in other DBMSes.

I tried this myself on one of my own tables and it didn't like being
ordered by count(*) but worked fine when the count() was given an alias
name.

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

[Back to original message]