Top N rows not working - Access / CF7

[Scott Doc]

I have a subquery that is meant to select only the TOP 1 matching row, however I was getting the: "At most one record can be returned by this subquery." error

This was weird considering I was using "Top 1" keyword in the subquery -- so I extracted the subquery - and exported the results. It returns 2 rows - IF the value is the same. (?!!)

Here is the query:
SELECT TOP 1 Tbl1.Name
From (((T2 INNER JOIN T3 ON T2.AID = T3.AID)
        INNER JOIN T4 ON T3.T4ID = T4.T4ID)
        INNER JOIN Tbl1 ON T4.PgID = Tbl1.PgID)
WHERE (T2.photoID = #GetResults.photoID#)
ORDER BY T2.T2ID

Why isn't TOP working like it's supposed to?? If the Name is the same (which is possible) it returns it twice! I assume if I include the T2.T2ID in the results it might return only 1 - but then that would be two columns and wouldn't work as a subquery.. right?

The DB is Access. Not sure the year (97, 2003 etc)

Thanks,
Scottdoc






~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3217
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Scott Doc]

Hello Me, Yes I found a solution for myself. So for any others that have this problem, simply add a "Group By"

So now:

SELECT TOP 1 Tbl1.Name
From (((T2 INNER JOIN T3 ON T2.AID = T3.AID)
        INNER JOIN T4 ON T3.T4ID = T4.T4ID)
        INNER JOIN Tbl1 ON T4.PgID = Tbl1.PgID)
WHERE (T2.photoID = #GetResults.photoID#)
GROUP BY T2.T2ID, Tbl1.Name
ORDER BY T2.T2ID

Any idea why this is? Didn't really seem like an obvious solution to me, but there you go.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3218
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Scott Doc]

I spoke too soon.
I was wrong.

It STILL doesn't work. I was testing with a maxrows="200" (my query returns roughly 2000 rows)

NOW it returns two rows, regardless of if they are the same or not. It's like it just ignores the "TOP 1"

(I also tried putting the 1 in quotes "Top (1)" .. that was a syntax error)

This is my error message.. please help!

Diagnostic: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] At most one record can be returned by this subquery.
The error occurred on line 307.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3219
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Peter Boughton]

Please tell me that those are not real table names!?!
Without knowing what you're trying to do with the query, it's hard to give
advice on how to correct the SQL.


Eitherway, my advice would be stop using Microsoft Access, and pick *any*
other database system out there.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3220
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Maya Tulchinsky]

Have you tried to use "distinct"?

--- On Sat, 5/23/09, Scott Doc <scott@...> wrote:

From: Scott Doc <scott@...>
Subject: Re: Top N rows not working - Access / CF7
To: "sql" <sql@...>
Date: Saturday, May 23, 2009, 9:22 PM


I spoke too soon.
I was wrong.

It STILL doesn't work. I was testing with a maxrows="200" (my query returns roughly 2000 rows)

NOW it returns two rows, regardless of if they are the same or not. It's like it just ignores the "TOP 1"

(I also tried putting the 1 in quotes "Top (1)" .. that was a syntax error)

This is my error message.. please help!

Diagnostic: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] At most one record can be returned by this subquery.
The error occurred on line 307.




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3221
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Scott Doc]

Distinct?? Interesting.. why hadn't I thought of that :) Thanks Maya!
No, actually I ended up solving it another way. I just added a few more keyIDs in the order by clause. It seemed to work even thought those fields were not appearing in the select statement. I'm posting here for anyone else having a similar problem.

And no, they're not real table names. I thought it wise to alias them on a public forum. And as for migrating from Access... yes.. a long term goal, certainly. But not really my decision at the moment. thanks anyway.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3231
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6