Returning results in order by cartain associated rows value

Question

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?

@JacobH Moving it to the top level makes the order by every attr_val in each row. I need it in order by item_id = 305, then item_id = 301 etc in the case above. — David Tunnell, Jun 20 '17 at 18:00
@JacobH That puts it in order 300, 301, 305 not 305, 301, 300. — David Tunnell, Jun 20 '17 at 18:07

S3S · Answer 1 · 2017-06-20T18:28:57.467

3

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

edited Jun 20 '17 at 18:28

answered Jun 20 '17 at 18:05

S3S

24,809
5
26
45

score 1 · Accepted Answer · answered Jun 20 '17 at 18:05

1

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)

answered Jun 20 '17 at 18:05

Gordon Linoff

1,242,037
58
646
786

Returning results in order by cartain associated rows value

2 Answers2