CODEWITHSUNDEEP
sqlsql-servert-sql
bicycle_guy
bicycle_guy

Reputation: 299

TSQL Select max value associated with an ID from a table joined on a different ID?

I have 3 tables structured like this:

Shipment
+-------------+--------------------+
| Shipment_ID | Shipment_ID_Master |
+-------------+--------------------+
|        4767 |               4767 |
|       88359 |              28431 |
+-------------+--------------------+

Factory
+------------+-------------+
| Factory_ID | Shipment_ID |
+------------+-------------+
|     338161 |        4767 |
|    1178567 |       88359 |
|    1178568 |       88359 |
+------------+-------------+

Coverage
+------------+-----------+----------+
| Factory_ID | Public_ID | Revision |
+------------+-----------+----------+
|     338161 |      2354 |        2 |
|    1178567 |     32436 |        4 |
|    1178568 |      2354 |        3 |
+------------+-----------+----------+

I am trying to build a view that displays a row for only the max Public_ID associated with a Shipment_ID. The view should look like this:

+-------------+--------------------+------------+-----------+----------+
| Shipment_ID | Shipment_ID_Master | Factory_ID | Public_ID | Revision |
+-------------+--------------------+------------+-----------+----------+
|        4767 |               4767 |     338161 |      2354 |        2 |
|       88359 |              28431 |    1178567 |     32436 |        4 |
+-------------+--------------------+------------+-----------+----------+

I have a query that works to build this view, but it is too slow. When my application joins on this view the query is taking several minutes to finish execution. This is the query:

SELECT  f.Shipment_ID,
        s.Shipment_ID_Master,
        f.Factory_ID,
        c.Public_ID,
        c.Revision
FROM Coverage c
JOIN Factory f ON c.Factory_ID = f.Factory_ID
JOIN Shipment s ON s.Shipment_ID = f.Shipment_ID
WHERE Public_ID = (
    SELECT MAX(Public_ID)
    FROM Coverage c2
    JOIN Factory f2 ON c2.Factory_ID = f2.Factory_ID
    WHERE f2.Shipment_ID = f.Shipment_ID
)

I think referencing this view is so slow because of the logic in the where clause. There must be a better and faster way to do this.

How can I select the maximum Public_ID associated with a Shipment_ID when the Shipment_ID is not stored on the same table as the Public_ID? Is it possible to do this without a where clause?

Upvotes: 2

Views: 31

Answers (1)

sacse
sacse

Reputation: 3744

You can use Row_number to get the better performance over the given solution.

Try the following:

;WITH cte AS
(
    SELECT s.Shipment_ID, s.Shipment_ID_Master, f.Factory_ID, c.Public_ID, c.Revision, row_number() OVER (PARTITION BY s.Shipment_ID ORDER BY c.Public_ID desc) AS rn
    FROM #Shipment s
    JOIN #Factory f ON f.Shipment_ID = s.Shipment_ID
    JOIN #Coverage c ON c.Factory_ID = f.Factory_ID
)
SELECT c.Shipment_ID, c.Shipment_ID_Master, c.Factory_ID, c.Public_ID, c.Revision 
FROM cte c WHERE rn = 1

Please see db<>fiddle here.

Upvotes: 2

Related Questions