Reputation: 7542
I have a query that pulls back some data.
SELECT *
FROM [contract_attr]
WHERE item_id IN (
SELECT item_id
FROM contract_attr
WHERE field_id = 234
AND attr_val IN (
SELECT attr_val
FROM contract_attr
WHERE field_id = 234
AND attr_val IN (
SELECT item_pk
FROM mfr
WHERE item_id = 13
)
)
)
Take a look at the rows where the field_id is 413. I need the order of the item_ids to be in alphabetical order where the attr_val of rows where field_id = 413 is what is ordered by. I hope that makes sense.
I make a query that does this:
SELECT item_id
FROM [contract_attr]
WHERE field_id = 413
AND item_id IN (
SELECT item_id
FROM [contract_attr]
WHERE attr_val = (
SELECT item_pk
FROM mfr
WHERE item_id = 13
)
)
ORDER BY attr_val
But when I add it to the query:
SELECT *
FROM [contract_attr]
WHERE item_id IN (
SELECT item_id
FROM [contract_attr]
WHERE field_id = 413
AND item_id IN (
SELECT item_id
FROM [contract_attr]
WHERE attr_val = (
SELECT item_pk
FROM mfr
WHERE item_id = 13
)
)
ORDER BY attr_val
)
I get The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
How do I fix this? I see here (The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions) that I cant use order by inside inner queries, but then how do I get the desired results?
Upvotes: 1
Views: 44
Reputation: 25122
All of your nested queries can be turned into a join and a where clause. There isn't any sense in querying the same table over and over again from what I can tell.
SELECT
a.*
FROM
[contract_attr] a
INNER JOIN
mfr r on
r.item_pk = a.attr_val
WHERE
a.field_id = 234
and r.item_id = 13
ORDER BY a.attr_val
Upvotes: 3
Reputation: 1269963
You can do this at the outermost level using a window function in the order by
:
order by max(case when field_id = 413 then attr_val end) over (partition by item_id)
Upvotes: 1