Improve company productivity with a Business Account.Sign Up

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

First 100 records in a query???

Hello
I have a customer table which contains 2000 records.
I want to view the first 100 records.
How do i do this????

Current Query is
strSql = "Select CustomerName from Customers;"

This shows all the records.

Regards Turlough
0
visualbasic
Asked:
visualbasic
  • 4
  • 4
  • 2
  • +4
1 Solution
 
procyn00Commented:
Select Top 100 customerName from customers;
0
 
carpediemCommented:
It depends on the database you are using.  For many databases procyn00's answer is by far the best answer.  However there are some database that do not support the "Top" syntax.  For those, you will need to find another method.  Often, you may be forced to just loop through the record set and use the first 100.  Of course this isn't very good since depending on the database and how you are retrieving the records often all 2000 records will still be retrieved and brought over the network.
0
 
bob_onlineCommented:
You're going to need an order by clause or you risk getting the top 100 random customers.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
DrDelphiCommented:
For a database which does not support "TOP":
'Amount is an integer filed

Select  t1.Amount from Checks t1 where 100> (select
     count(t2.Amount) from checks t2 where t2.Amount>t1.Amount)


Good luck!!
0
 
procyn00Commented:
If you use a Record ID that increments with every new record or a timestamp field you can just..

Select Top 100 customerName from customers ORDER BY Record_ID_FIELD DESC;
0
 
procyn00Commented:
What databases do not support the Top Operator? I'm not aware of any and would just like to make note.
0
 
Anthony PerkinsCommented:
>>What databases do not support the Top Operator? I'm not aware of any and would just like to make note. <<
Top keyword was introduced with SQL Server 7.0, prior versions did not support it.
I am not sure, but I believe it is not supported in either Oracle or MySQL

Anthony


0
 
DrDelphiCommented:
Paradox for one. Interbase for another. I'm not sure, but I think that VFP doesn't either.


0
 
DrDelphiCommented:
Actually, Oracle does support TOP (or at least versions 7.3 and up do). I can't say if MySQL does or not.


0
 
Anthony PerkinsCommented:
Thanks for the correction.

Anthony
0
 
procyn00Commented:
MySQL Uses Limit

Select customerName from customers LIMIT 100 ORDER BY Record_ID_FIELD DESC
0
 
aranaCommented:
NOP, i'm using oraqcle 7.3.4.3.1 and it DOES NOT support
"TOP"  clause


in oracle you have to do something like :

SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
FROM
(SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
FROM TOP20 where Customer='Canada'
and Priority='Minor') where rownum<100 ORDER BY TotalActiveDays desc;


0
 
DrDelphiCommented:
Hmmmm... It's been a while, but I am almost positive that I used TOP with version 7.3.x? of Oracle. I know for a fact that it is in 8.x... strange. I'll have to review some of my old code now....<g>

0
 
rpaiCommented:
To view the first 100 records::

strSql = "SELECT CustomerName FROM Customers WHERE Rownum < 101;"
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 4
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now