Top in SQL

[GH]

I'm trying to use the "TOP" expression in SQL for Access. However, I
get all of the records returned instead of the top 2 that I'm looking
for. I don't get any errors either. It seems to be ignored. Here's
the SQL I'm using:

SELECT TOP 2 autoQuesID, fldQuesTitle
FROM tblCNT_Question
WHERE ( blnLive = TRUE AND blnHmpgNews = TRUE AND dtToPost <= Now() AND
dtToRemove > Now() ) ORDER BY numOrder;

There's 3 total rows in the table. The recordset returns all 3, which
it should. However, I have the "TOP 2" phrase in the SQL and it still
returns all 3 with no errors or anything.

Any ideas?

Thanks!

[Bob Barrows [MVP]]

GH wrote:

> I'm trying to use the "TOP" expression in SQL for Access. However, I
> get all of the records returned instead of the top 2 that I'm looking
> for. I don't get any errors either. It seems to be ignored. Here's
> the SQL I'm using:
>
> SELECT TOP 2 autoQuesID, fldQuesTitle
> FROM tblCNT_Question
> WHERE ( blnLive = TRUE AND blnHmpgNews = TRUE AND dtToPost <= Now()
> AND dtToRemove > Now() ) ORDER BY numOrder;
>
> There's 3 total rows in the table. The recordset returns all 3, which
> it should. However, I have the "TOP 2" phrase in the SQL and it still
> returns all 3 with no errors or anything.
>
> Any ideas?
>
> Thanks!

How many rows does this query return when you run it in Access using the
Access Query Builder? If it returns 2 in that environment, then the problem
is with the code in your asp page rather than the query itself. Needless to
say, we can't help with that without seeing the code and, if you are using
dynamic sql, the result of Response.Write sqlvariable.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron [SQL Server MVP]]

Is numOrder distinct? If there are ties, it will return them...

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"GH" <anonymous@discussions.nowhere.com> wrote in message
news:uvOlamdaEHA.2792@TK2MSFTNGP09.phx.gbl...

> I'm trying to use the "TOP" expression in SQL for Access. However, I
> get all of the records returned instead of the top 2 that I'm looking
> for. I don't get any errors either. It seems to be ignored. Here's
> the SQL I'm using:
>
> SELECT TOP 2 autoQuesID, fldQuesTitle
> FROM tblCNT_Question
> WHERE ( blnLive = TRUE AND blnHmpgNews = TRUE AND dtToPost <= Now() AND
> dtToRemove > Now() ) ORDER BY numOrder;
>
> There's 3 total rows in the table. The recordset returns all 3, which
> it should. However, I have the "TOP 2" phrase in the SQL and it still
> returns all 3 with no errors or anything.
>
> Any ideas?
>
> Thanks!

[GH]

Bob Barrows [MVP] wrote:

> GH wrote:
>

>>I'm trying to use the "TOP" expression in SQL for Access. However, I
>>get all of the records returned instead of the top 2 that I'm looking
>>for. I don't get any errors either. It seems to be ignored. Here's
>>the SQL I'm using:
>>
>>SELECT TOP 2 autoQuesID, fldQuesTitle
>>FROM tblCNT_Question
>>WHERE ( blnLive = TRUE AND blnHmpgNews = TRUE AND dtToPost <= Now()
>>AND dtToRemove > Now() ) ORDER BY numOrder;
>>
>>There's 3 total rows in the table. The recordset returns all 3, which
>>it should. However, I have the "TOP 2" phrase in the SQL and it still
>>returns all 3 with no errors or anything.
>>
>>Any ideas?
>>
>>Thanks!

>
>
> How many rows does this query return when you run it in Access using the
> Access Query Builder? If it returns 2 in that environment, then the problem
> is with the code in your asp page rather than the query itself. Needless to
> say, we can't help with that without seeing the code and, if you are using
> dynamic sql, the result of Response.Write sqlvariable.
>
> Bob Barrows
>

Well, I tried it in the Access Query Builder and I get three rows. It's
almost as if it is complete ignoring the "Top 2" portion of the SQL
statement. Any other ideas?

Thanks again

[GH]

Aaron [SQL Server MVP] wrote:

> Is numOrder distinct? If there are ties, it will return them...
>

Good call. I just removed the ORDER BY clause and it returned only two
records. Here's how I modified the SQL statement that seems to be
returning only two rows now:

SELECT TOP 2 autoQuesID, fldQuesTitle
FROM tblCNT_Question
WHERE ( blnLive = TRUE AND blnHmpgNews = TRUE AND dtToPost <= Now() AND
dtToRemove > Now() )
ORDER BY numOrder,fldQuesTitle;

Thanks Aaron

[Dan]

GH wrote:

> I'm trying to use the "TOP" expression in SQL for Access. However, I
> get all of the records returned instead of the top 2 that I'm looking
> for. I don't get any errors either. It seems to be ignored. Here's
> the SQL I'm using:
>
> SELECT TOP 2 autoQuesID, fldQuesTitle
> FROM tblCNT_Question
> WHERE ( blnLive = TRUE AND blnHmpgNews = TRUE AND dtToPost <= Now() AND
> dtToRemove > Now() ) ORDER BY numOrder;
>
> There's 3 total rows in the table. The recordset returns all 3, which
> it should. However, I have the "TOP 2" phrase in the SQL and it still
> returns all 3 with no errors or anything.
>
> Any ideas?
>
> Thanks!

Call me crazy, im new here. but couldn't you do this?

SELECT autoQuesID, fldQuesTitle
FROM tblCNT_Question
WHERE ( blnLive = TRUE AND blnHmpgNews = TRUE AND dtToPost <= Now() AND
dtToRemove > Now() ) ORDER BY numOrder LIMIT 2;

[Bob Barrows [MVP]]

Dan wrote:

> GH wrote:
>

>> I'm trying to use the "TOP" expression in SQL for Access. However, I
>> get all of the records returned instead of the top 2 that I'm looking
>> for. I don't get any errors either. It seems to be ignored. Here's
>> the SQL I'm using:
>>
>> SELECT TOP 2 autoQuesID, fldQuesTitle
>> FROM tblCNT_Question
>> WHERE ( blnLive = TRUE AND blnHmpgNews = TRUE AND dtToPost <= Now()
>> AND dtToRemove > Now() ) ORDER BY numOrder;
>>
>> There's 3 total rows in the table. The recordset returns all 3,
>> which it should. However, I have the "TOP 2" phrase in the SQL and
>> it still returns all 3 with no errors or anything.
>>
>> Any ideas?
>>
>> Thanks!

> Call me crazy, im new here. but couldn't you do this?
>
> SELECT autoQuesID, fldQuesTitle
> FROM tblCNT_Question
> WHERE ( blnLive = TRUE AND blnHmpgNews = TRUE AND dtToPost <= Now()
> AND dtToRemove > Now() ) ORDER BY numOrder LIMIT 2;

No. JetSQL does not support the LIMIT keyword.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron [SQL Server MVP]]

> Call me crazy, im new here. but couldn't you do this?

You're crazy! Did you try that query in Access?

>
> SELECT autoQuesID, fldQuesTitle
> FROM tblCNT_Question
> WHERE ( blnLive = TRUE AND blnHmpgNews = TRUE AND dtToPost <= Now() AND
> dtToRemove > Now() ) ORDER BY numOrder LIMIT 2;