SQL - SELECT TOP 10 issues

[myflashstore]

Hi,

I have a table full of purchased items and i want to be able to select the top 10 most purchased products for a given user and I want to return the ID of those along with how many instances of that ID exists.

I'm using the following SQL code

SELECT TOP (10) beat_id, Count(*)
FROM jos_mfs_items
WHERE producer = 100
GROUP BY beat_id
ORDER BY COUNT(*) DESC

But it doesn't return anything, does anyone have any clues why?

Thanks

 

[r937]

which database system are you on?

if i'm not mistaken, "jos_xxxx" indicates joomla, a popular content management system, and joomla runs on MySQL

you should realize that MySQL does not support TOP, which is used exclusively by microsoft Access and SQL Server

nor should this question be in the ANSI SQL forum

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MarcLodge]

Not sure if this is ANSI SQL as I'm a DB2 man myself, but If I were doing it in that environment I'd do:

SELECT beat_id, numb
from (
      SELECT beat_id, Count(*) as numb 
      FROM jos_mfs_items 
      WHERE producer = 100 
      GROUP BY beat_id
     ) a 
ORDER BY numb DESC
fetch first 10 rows only

 

[r937]

you would rewrite joomla's database interface to use db2?



;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon