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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
You're going to need an order by clause or you risk getting the top 100 random customers.
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!!
'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!!
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;
Select Top 100 customerName from customers ORDER BY Record_ID_FIELD DESC;
What databases do not support the Top Operator? I'm not aware of any and would just like to make note.
>>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
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
Paradox for one. Interbase for another. I'm not sure, but I think that VFP doesn't either.
Actually, Oracle does support TOP (or at least versions 7.3 and up do). I can't say if MySQL does or not.
Thanks for the correction.
Anthony
Anthony
MySQL Uses Limit
Select customerName from customers LIMIT 100 ORDER BY Record_ID_FIELD DESC
Select customerName from customers LIMIT 100 ORDER BY Record_ID_FIELD DESC
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;
"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;
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>
To view the first 100 records::
strSql = "SELECT CustomerName FROM Customers WHERE Rownum < 101;"
strSql = "SELECT CustomerName FROM Customers WHERE Rownum < 101;"