David Tunnell David Tunnell - 4 months ago 9
SQL Question

Returning results in order by cartain associated rows value

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
)
)
)


enter image description here

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


enter image description here

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?

Answer Source

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)