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
visualbasicAsked:
Who is Participating?
 
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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.

All Courses

From novice to tech pro — start learning today.