[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

ANSI Compliant keyword for TOP N clause

Posted on 2004-09-02
5
Medium Priority
?
349 Views
Last Modified: 2012-08-14
Need to find ANSI-92 compliant keyword for the TOP (n) keyword used in SQL Server Transact SQL?
0
Comment
Question by:dbatrainer
  • 3
5 Comments
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11962776
select foo, bar
  from yourtable X
 where ( select count(*)
           from yourtable  
          where foo > X.foo ) < 10

Jay
0
 
LVL 11

Expert Comment

by:SweatCoder
ID: 11965117
use LIMIT in MySQL and maybe others as well.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 11965963
Which Database are you using??

Jay
0
 

Author Comment

by:dbatrainer
ID: 11966443
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
 
LVL 10

Accepted Solution

by:
Jay Toops earned 2000 total points
ID: 11968164
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question