在SQL Server 2012中关联两表获取符合条件的最新idorder

Alright, let's work through this problem step by step. You need the most recently entered idorder (since it's an identity column, higher values mean newer records) where the order has at least one line item with product # = 97 and imgexists = True. Here are two reliable approaches tailored for SQL Server 2012:

Approach 1: Using MAX() Aggregation #

This is straightforward if you only need the latest idorder value:

SELECT MAX(ch.idorder) AS LatestIdOrder
FROM carthead ch
INNER JOIN cartrows cr 
  ON ch.idorder = cr.idorder -- Assumes cartrows uses idorder to link to carthead
WHERE cr.[product #] = 97 
  AND cr.imgexists = 1 -- Use 'True' instead if imgexists is a string (e.g., VARCHAR) type

Approach 2: Using TOP 1 with Sorting #

This method is great if you might need to fetch additional details from the carthead table later—just add more columns to the SELECT clause:

SELECT TOP 1 ch.idorder AS LatestIdOrder
FROM carthead ch
INNER JOIN cartrows cr 
  ON ch.idorder = cr.idorder
WHERE cr.[product #] = 97 
  AND cr.imgexists = 1
ORDER BY ch.idorder DESC -- Sort orders from newest to oldest, pick the first one

Key Notes: #

• I assumed cartrows has an idorder column to join with carthead—this is the standard way to link header and detail tables. If your actual join column has a different name, adjust the ON condition accordingly.
• Identity columns in SQL Server auto-increment, so the highest idorder value corresponds to the most recently created order.
• If imgexists is a string type (not a BIT), replace 1 with 'True' in the WHERE clause to match your data type.

内容的提问来源于stack exchange，提问作者jasonCbraatz