Search code examples
sqlsql-servert-sqlsubquerygreatest-n-per-group

Finding Max Value on Inner Join - SQL

I have a temp table, lets call it #order, within this table there are multiple records of order history with changes.

#order table

I'm looking to see the max value of the column or_chgn in which scales in the above example with all changes made to an order file.

However, when I write my query, the result I get is in error.

Query

SELECT * 
FROM t.#ORDER
INNER JOIN (
    SELECT OR_ORDN, max(OR_CHGN) OR_CHGN
    FROM t.#ORDER
    GROUP BY OR_ORDN
    ) d
ON t.OR_ORDN = d.OR_ORDN 
and t.OR_CHGN = d.OR_CHGN

Message:

Msg 4104, Level 16, State 1, Line 16

The multi-part identifier "t.OR_ORDN" could not be bound.

Msg 4104, Level 16, State 1, Line 17

The multi-part identifier "t.OR_CHGN" could not be bound.

What am I missing here?

Solution

  • You need to declare alias t in the from clause of the outer query, so you can then refer to it in the ON clause of the JOIN.

    Since it looks like your schema is called t already, I am changing the aliases to make things clearer:

    SELECT * 
FROM t.#ORDER o  --> here
INNER JOIN (
    SELECT OR_ORDN, max(OR_CHGN) OR_CHGN
    FROM t.#ORDER
    GROUP BY OR_ORDN
) omax ON omax.OR_ORDN = o.OR_ORDN AND omax.OR_CHGN = o.OR_CHGN

    Another commonly-used approach at this greatest-n-per-group problem is to use a correlated subquery, which somehow simplifies the query:

    SELECT * 
FROM t.#ORDER o  
WHERE OR_CHGN = (
    SELECT MAX(o1.OR_CHGN) FROM t.#ORDER o1 WHERE o1.OR_ORDN = o.OR_ORDN
)

    This query would take advantage of an index on (OR_ORDN, OR_CHGN).