David Tunnell
David Tunnell

Reputation: 7542

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?

Upvotes: 1

Views: 44

Answers (2)

S3S
S3S

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

Gordon Linoff
Gordon Linoff

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

Related Questions