|
Home > Archive > Programming with dBASE > December 2005 > sql limit
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| John Noble 2005-12-07, 9:23 am |
| In my app I need to extract a specific number of records from a table. So I am trying to use the 'limit' keyword as such:
'select * from my table limit 50'
Using the above code, I receive a 'token not found'
Is there an alternative method to perform this task
| |
| David Kerber 2005-12-07, 11:23 am |
| In article <m8ODI0z#FHA.2328@news-server>, john@nor-tech.co.uk says...
> In my app I need to extract a specific number of records from a table. So I am trying to use the 'limit' keyword as such:
> 'select * from my table limit 50'
>
> Using the above code, I receive a 'token not found'
>
> Is there an alternative method to perform this task
You didn't actually have the space between 'my' and 'table' in the real
case, did you?
The most common (there's no ANSI standard, AFAIK) syntax for limiting
the number of records returned is the TOP clause:
select top 50 * from mytable
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Roland Wingerter 2005-12-07, 11:23 am |
| John Noble wrote
> In my app I need to extract a specific number of records from a table. So
> I am trying to use the 'limit' keyword as such:
> 'select * from my table limit 50'
-------
AFAIK, the 'limit' keyword (or 'top' keyword mentioned by David) are not
supported by local SQL.
Roland
| |
| JohnNoble 2005-12-07, 11:23 am |
| David Kerber Wrote:
>
> The most common (there's no ANSI standard, AFAIK) syntax for limiting
> the number of records returned is the TOP clause:
>
> select top 50 * from mytable
>
Tried the above method, however was prompted with:
"Invalid use of keyword: 50"
Any other ideas??
John
| |
| *Lysander* 2005-12-07, 11:23 am |
| In article <rGg08w0#FHA.2316@news-server>, john@nor-tech.co.uk says...
> Tried the above method, however was prompted with:
> "Invalid use of keyword: 50"
>=20
> Any other ideas??
make the query read-only (by setting requestlive =3D .F.) and see if this=
=20
helps... there are strange things going on in the BDE about such=20
limitations....
--=20
ciao,
Andr=E9
| |
| David Kerber 2005-12-07, 1:23 pm |
| In article <rGg08w0#FHA.2316@news-server>, john@nor-tech.co.uk says...
> David Kerber Wrote:
>
> Tried the above method, however was prompted with:
> "Invalid use of keyword: 50"
>
> Any other ideas??
I'm afraid not; it looks like Roland is correct in that the TOP clause
is not supported by local SQL.
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
|
| HI,
First you have to mention the limiting field, otherise its the top 50 of
what, which field.
so the sql should be select myfield top 50 from my table.
However as others have mentioned the keyword top is not supported correctly
by localsql, so in youre query make the request live to false and it needs
to be executed agains a true SQL server, like MS SQL or others.
so this works
d = new database("rassql")
q = new query()
q.database = d
q.requestlive = false
q.sql = "select top 10 CompanyID from company"
q.active = true
? q.rowset.count() /////returns 10
"JohnNoble" <john@nor-tech.co.uk> wrote in message
news:rGg08w0#FHA.2316@news-server...
> David Kerber Wrote:
>
> Tried the above method, however was prompted with:
> "Invalid use of keyword: 50"
>
> Any other ideas??
>
> John
| |
|
|
"rb" <me@u.com> wrote in message news:sDkU2Q$#FHA.2332@news-server...
> HI,
> First you have to mention the limiting field, otherise its the top 50 of
> what, which field.
Or you can use the orderby clause
select top 10 * from company order by CompanyID
Robert
| |
| David Kerber 2005-12-08, 7:23 am |
| In article <sDkU2Q$#FHA.2332@news-server>, me@u.com says...
> HI,
> First you have to mention the limiting field, otherise its the top 50 of
> what, which field.
In databases which support the TOP clause, you're limiting the number of
*records* returned, so "SELECT TOP 50 * FROM mytable" is still valid. I
do it all the time if I want all fields.
> so the sql should be select myfield top 50 from my table.
> However as others have mentioned the keyword top is not supported correctly
> by localsql, so in youre query make the request live to false and it needs
> to be executed agains a true SQL server, like MS SQL or others.
> so this works
> d = new database("rassql")
> q = new query()
> q.database = d
> q.requestlive = false
> q.sql = "select top 10 CompanyID from company"
> q.active = true
> ? q.rowset.count() /////returns 10
>
>
> "JohnNoble" <john@nor-tech.co.uk> wrote in message
> news:rGg08w0#FHA.2316@news-server...
>
>
>
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Robert Bravery 2005-12-09, 3:23 am |
| HI,
Not to be argumentative, but this is not how I understand it. From what I
have read, the select top, need something to retunr and compare with. The
top of what, top 10 records. What is the comparison. Cant be record numbers,
is it customer ID, or Zip code, or Salary, etc. Like a filter, you need to
filter on something. By stating the field number, or using an orderby
clause, you are stating that you whant the top 10 of that particular sele
ction.
From the MSDN site:
"If the query includes an ORDER BY clause, the first n rows (or n percent of
rows) ordered by the ORDER BY clause are output. If the query has no ORDER
BY clause, the order of the rows is arbitrary"
If I am incorrect, please explain.
"David Kerber" < ns_dkerber@ns_Warren
RogersAssociates.com> wrote in message
news:MPG. 1e01f915301b1af1989d
72@news.dbase.com...
> In article <sDkU2Q$#FHA.2332@news-server>, me@u.com says...
>
> In databases which support the TOP clause, you're limiting the number of
> *records* returned, so "SELECT TOP 50 * FROM mytable" is still valid. I
> do it all the time if I want all fields.
>
>
correctly[color=dark
red]
needs[color=darkred]
limiting[color=darkr
ed]
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).
| |
| David Kerber 2005-12-09, 9:23 am |
| In article <N3v2axI$FHA.2316@news-server>, me@u.com says...
> HI,
> Not to be argumentative, but this is not how I understand it. From what I
> have read, the select top, need something to retunr and compare with. The
> top of what, top 10 records. What is the comparison. Cant be record numbers,
> is it customer ID, or Zip code, or Salary, etc. Like a filter, you need to
> filter on something. By stating the field number, or using an orderby
> clause, you are stating that you whant the top 10 of that particular sele
> ction.
> From the MSDN site:
> "If the query includes an ORDER BY clause, the first n rows (or n percent of
> rows) ordered by the ORDER BY clause are output. If the query has no ORDER
> BY clause, the order of the rows is arbitrary"
>
> If I am incorrect, please explain.
You are reading too much into the description. If you replace
"records" in my previous description with "rows", you get the same thing
as the MSDN description. The TOP N clause simply returns the *first N
rows retrieved by the SELECT statement*. If you have an ORDER BY
clause, then you can control what ones are retrieved. If there is no
ORDER BY clause, then you get the first N rows it happens to come across
while executing the SELECT, and which ones those are may vary depending
on the execution plan the SQL engine decides to use.
Try:
SELECT TOP 50 * from anytable
It will work, but you may or may not get the same rows back each time
you run it. In reality, you probably will get the same ones back if you
haven't added or deleted any rows from the table in the mean time,
because the SQL engine will normally use the same access plan each time.
>
> "David Kerber" < ns_dkerber@ns_Warren
RogersAssociates.com> wrote in message
> news:MPG. 1e01f915301b1af1989d
72@news.dbase.com...
> correctly
> needs
> limiting
>
>
>
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Robert Bravery 2005-12-09, 1:23 pm |
| Hi,
Yeah I get that. But then surely if you want the top 10, you want the top 10
of something, not just anything. Does'nt make sense. Ok I understand that it
works, but is it logical.
Robert
"David Kerber" < ns_dkerber@ns_Warren
RogersAssociates.com> wrote in message
news:MPG. 1e03585e65d97af8989d
74@news.dbase.com...
> In article <N3v2axI$FHA.2316@news-server>, me@u.com says...
I[color=darkred]
The[color=darkred]
numbers,[color=darkr
ed]
to[color=darkred]
sele[color=darkred]
percent of[color=darkred]
ORDER[color=darkred]
>
> You are reading too much into the description. If you replace
> "records" in my previous description with "rows", you get the same thing
> as the MSDN description. The TOP N clause simply returns the *first N
> rows retrieved by the SELECT statement*. If you have an ORDER BY
> clause, then you can control what ones are retrieved. If there is no
> ORDER BY clause, then you get the first N rows it happens to come across
> while executing the SELECT, and which ones those are may vary depending
> on the execution plan the SQL engine decides to use.
>
> Try:
>
> SELECT TOP 50 * from anytable
>
> It will work, but you may or may not get the same rows back each time
> you run it. In reality, you probably will get the same ones back if you
> haven't added or deleted any rows from the table in the mean time,
> because the SQL engine will normally use the same access plan each time.
>
>
>
message[color=darkre
d]
50 of[color=darkred]
of[color=darkred]
I[color=darkred]
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).
| |
| David Kerber 2005-12-12, 9:23 am |
| In article <lEoboRP$FHA.1020@news-server>, me@u.com says...
> Hi,
>
> Yeah I get that. But then surely if you want the top 10, you want the top 10
> of something, not just anything. Does'nt make sense. Ok I understand that it
> works, but is it logical.
Now I see what you're getting at. I use the "SELECT TOP 100 * FROM
mytable" quite a bit when I'm in the early stages of development, just
to see if data (any data) is getting stored away properly. As you say,
for production work you would need to have an ORDER BY clause to get
something useful out of it.
Dave
....
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Robert Bravery 2005-12-12, 11:23 am |
| Ok now we are on the same page
RObert
"David Kerber" < ns_dkerber@ns_Warren
RogersAssociates.com> wrote in message
news:MPG. 1e03c26d365f91c5989d
76@news.dbase.com...
> In article <lEoboRP$FHA.1020@news-server>, me@u.com says...
top 10[color=darkred]
that it[color=darkred]
>
> Now I see what you're getting at. I use the "SELECT TOP 100 * FROM
> mytable" quite a bit when I'm in the early stages of development, just
> to see if data (any data) is getting stored away properly. As you say,
> for production work you would need to have an ORDER BY clause to get
> something useful out of it.
>
> Dave
>
> ...
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).
| |
| David Kerber 2005-12-12, 8:23 pm |
| In article <p0saY1z$FHA.2328@news-server>, me@u.com says...
> Ok now we are on the same page
We always were, but were talking about different books <grin>.
.....
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
|
|
|
|
|