How to get "SELECT MAX" to work in a WHERE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get "SELECT MAX" to work in a WHERE

Author

Topic

paulkon
Starting Member

3 Posts

Posted - 2009-08-21 : 02:32:11

I think I'm close on this one, but can't quite bring it home.

I can get this to run fine, which I filter on just VersionID=8.
===========
SELECT r.ResponseId, r.QuestionId, o.OriginalOptionIndex AS "OptionIndex", t.Response AS "TextResponse", r.QuestionText, b.Response AS "BinaryResponse", r.VersionId
FROM Responses r
LEFT OUTER JOIN TextResponses t ON r.ResponseId = t.ResponseId
LEFT OUTER JOIN BinaryResponses b ON r.ResponseId = b.ResponseId
LEFT OUTER JOIN ResponseOptions o ON o.OptionValue=t.Response
WHERE r.QuestionId<>'MainMenu' AND o.VersionID=8
ORDER BY r.ResponseId
=============

and I can get this to run fine, where I figure out that the maximum value in the column in question is 8.
======
(SELECT MAX(VersionID) FROM ResponseOptions)
======

but when I try to combine the two:
==========
SELECT r.ResponseId, r.QuestionId, o.OriginalOptionIndex AS "OptionIndex", t.Response AS "TextResponse", r.QuestionText, b.Response AS "BinaryResponse", r.VersionId
FROM Responses r
LEFT OUTER JOIN TextResponses t ON r.ResponseId = t.ResponseId
LEFT OUTER JOIN BinaryResponses b ON r.ResponseId = b.ResponseId
LEFT OUTER JOIN ResponseOptions o ON o.OptionValue=t.Response
WHERE r.QuestionId<>'MainMenu' AND o.VersionID=(SELECT MAX(VersionID) FROM ResponseOptions)
ORDER BY r.ResponseId
=============

I get an error parsing the query, at the SELECT MAX. Any suggestions?

thanks,

Paul.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-21 : 02:43:05

What is the exact error message?

Madhivanan

Failing to plan is Planning to fail

paulkon
Starting Member

3 Posts

Posted - 2009-08-21 : 02:50:28

Here's the error message, which points at the beginning of the word SELECT:

There was an error parsing the query. [ Token line number = 6, Token line offset = 49, Token in error = SELECT ]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-21 : 03:15:19

So, you are using MySQL and not MS SQL Server?

Madhivanan

Failing to plan is Planning to fail

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-08-21 : 03:19:17

what version of SQL Server are you using? CE? if so, i don't think nested selects are allowed

Em

paulkon
Starting Member

3 Posts

Posted - 2009-08-21 : 03:41:42

Good call--yes this is an SDF database I pulled off of a data collection app that runs on my windows mobile 5 phone. I downloaded the SDF to my machine and I'm running the query using a desktop tool.

Thus if nested selects aren't allowed, are there other ways to get this to work? Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources