SELECT TOP 1 Returning more than 1 record - MS Access

rito1
rito1 used Ask the Experts™
on
Hi All,

Can anyone see why the following SQL would ignore my request of only returning 1 record and returns multiple records in MS Access?

Many thanks,

Rit


SELECT TOP 1 tblQuestions.questionID AS nextQuestionID
FROM tblQuestions
WHERE (((tblQuestions.questionID)>[paramQuestionID]) AND ((tblQuestions.removed)=0))
ORDER BY tblQuestions.Seqorder;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
what about just using this query

SELECT TOP 1 tblQuestions.questionID AS nextQuestionID
FROM tblQuestions
ORDER BY tblQuestions.Seqorder Desc;

what did you get?
It's the order by that is introducing the problem because Access doesn't treat TOP the same as other databases...

Take out the order (shouldn't be needed for top 1 anyway) and it should work.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
and just to be 100% sure you want 1 per "item":
http://www.experts-exchange.com/A_3203.html

Author

Commented:
It seems to be because there are multiple instances of the Seqorder value. But this has been implemented by design. But I only want the first one it comes across :-(
Yes that would be exactly right - since you only want the first one it comes across, is the order important on Seqorder value?
Commented:
SELECT TOP 1 tblQuestions.questionID AS nextQuestionID
FROM tblQuestions
WHERE (((tblQuestions.questionID)>[paramQuestionID]) AND ((tblQuestions.removed)=0))
ORDER BY tblQuestions.Seqorder, tblQuestions.questionID;
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Try with Distinct:
SELECT DISTINCT TOP 1 tblQuestions.questionID AS nextQuestionID
FROM tblQuestions
WHERE (((tblQuestions.questionID)>[paramQuestionID]) AND ((tblQuestions.removed)=0))
ORDER BY tblQuestions.Seqorder;

Open in new window


/gustav

Author

Commented:
Thanks all. I sorted this issue out last night (not that I can remember how exactly now) :-)
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I guess you used the solution above!

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial