September 21, 2011 at 2:59 pm
I get this error when trying to run this query:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SELECT MAX(DayKey),Port_ID
FROM [USTG_ODS].dbo.tblSTAGE_INT_Portfolio WHERE Port_ID =
(
SELECT DISTINCT Port_ID
FROM USTG_ODS.dbo.tblSTAGE_INT_Portfolio
)
GROUP BY port_id
I am trying to get the row which has the MAX DayKey and its associated Port_ID. Otherwise, I end up with duplicate Port_IDs. I know there must be something to do with a JOIN here, but I can't seem to get it.
September 21, 2011 at 3:06 pm
You need to use an in keyword instead of =
Your sub query is return more then one row, but your where clause before the sub query has an =.
If you replace it with an in it should work fine.
Ben
September 21, 2011 at 3:10 pm
duanecwilson (9/21/2011)
I get this error when trying to run this query:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SELECT MAX(DayKey),Port_ID
FROM [USTG_ODS].dbo.tblSTAGE_INT_Portfolio WHERE Port_ID =
(
SELECT DISTINCT Port_ID
FROM USTG_ODS.dbo.tblSTAGE_INT_Portfolio
)
GROUP BY port_id
I am trying to get the row which has the MAX DayKey and its associated Port_ID. Otherwise, I end up with duplicate Port_IDs. I know there must be something to do with a JOIN here, but I can't seem to get it.
How are you getting duped port_ids if you do this:
SELECT port_ID, MAX(DayKey) AS MaxDayKey
FROM [USTG_ODS].dbo.tblSTAGE_INT_Portfolio
GROUP BY port_id
?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2011 at 3:23 pm
That was it. I was close and didn't see it. Thanks.
September 21, 2011 at 3:28 pm
The above post solved my problem. To answer your question, the inner query gave me more than one port_id. I misspoke when I said duplicates because I had been working on a larger query that surrounded this one and it was giving me duplicates. This particular inner query just gave me the error message I spoke of because it gave me a list of port_ids.
Thank you for the challenge, though, because it made me think, and I need to more accurately word my questions.
September 21, 2011 at 5:39 pm
Glad I could help... if a bit from the side-like. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 22, 2011 at 9:53 am
duanecwilson (9/21/2011)
I had been working on a larger query that surrounded this one
In that case, you might consider using a CROSS APPLY instead of Max().
SELECT DayKey, Port_ID
FROM <<Main Query>>
CROSS APPLY (
SELECT TOP (1) DayKey, Port_ID
FROM USTG_ODS.dbo.tblStage_INT_Portfolio
WHERE <<conditions>>
ORDER BY DayKey DESC
) AS DayKey
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply