Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

ANSI Compliant keyword for TOP N clause

Need to find ANSI-92 compliant keyword for the TOP (n) keyword used in SQL Server Transact SQL?
0
dbatrainer
Asked:
dbatrainer
  • 3
1 Solution
 
Jay ToopsCommented:
select foo, bar
  from yourtable X
 where ( select count(*)
           from yourtable  
          where foo > X.foo ) < 10

Jay
0
 
SweatCoderCommented:
use LIMIT in MySQL and maybe others as well.
0
 
Jay ToopsCommented:
Which Database are you using??

Jay
0
 
dbatrainerAuthor Commented:
comment to JLtoops from dbatrainer.

You're right a subquery would work, but it's resource intensive and I was hoping to find a universal ANSI compliant clause that works the same as TOP (n) for a hetergenous environment including SQL Server 2000, Oracle, Dbase, and others. One that might also allow (with ties).

Thanks for the reply
0
 
Jay ToopsCommented:
There isn't one at least in ANSI-92 ..
thats why i gave you the count query.
thats the most UNIVERSALLY likely to work
... unfortunately its a resource pig.

the other way i thought of was to use a cursor.
But I very much doubt that it would be less expensive than
the query i just gave you.

Jay
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now