×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL - SELECT TOP 10 issues

SQL - SELECT TOP 10 issues

SQL - SELECT TOP 10 issues

(OP)
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

RE: SQL - SELECT TOP 10 issues

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

RE: SQL - SELECT TOP 10 issues

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:

CODE

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

 

RE: SQL - SELECT TOP 10 issues

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



winky smile

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close