CODEWITHSUNDEEP
sqlsql-servert-sql
JL_Coder
JL_Coder

Reputation: 164

Select max column from joined tables

The joined tables will result as a view like below. I wish to select just 1 record of the max id or prod_month column.

con_model srt_value_current con_id prod_month id
model   4   49  37  45145
model   4   49  38  45726
    
SELECT DISTINCT TOP (100) PERCENT dbo.DM_TBL_CONFIGURATION_MODEL.con_model, dbo.SRT_Data.SRT_VALUE_CURRENT, dbo.DM_TBL_CONFIGURATION_MODEL.con_id, dbo.SRT_Data.ID, dbo.SRT_Data.PROD_MONTH
FROM            dbo.DM_TBL_CONFIGURATION_MODEL LEFT OUTER JOIN
                         dbo.SRT_ItemNumbers ON dbo.DM_TBL_CONFIGURATION_MODEL.con_model = dbo.SRT_ItemNumbers.ITEM_NUMBER LEFT OUTER JOIN
                         dbo.SRT_Data ON dbo.SRT_ItemNumbers.ID = dbo.SRT_Data.ITEM_NUMBER_ID
        WHERE (SRT_Data.id) IN 
                            ( SELECT MAX(id)
                              FROM SRT_Data
                            )
                            and con_model='model'

Upvotes: 0

Views: 59

Answers (2)

JL_Coder
JL_Coder

Reputation: 164

I am using Cross Apply.

             SELECT *
                    FROM dbo.DM_TBL_CONFIGURATION_MODEL a CROSS APPLY 
                        (
                            Select  Top 1 b.ID,dbo.SRT_Data.SRT_VALUE_CURRENT
                            From     dbo.SRT_ItemNumbers b LEFT OUTER JOIN
                         dbo.SRT_Data ON b.ID = dbo.SRT_Data.ITEM_NUMBER_ID
                            Where   b.ITEM_NUMBER = a.con_model
                            Order By ID Desc
                      ) X

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If you want one row, use TOP (1):

SELECT TOP (1) cm.con_model, s.SRT_VALUE_CURRENT, cm.con_id, s.ID, s.PROD_MONTH
FROM dbo.DM_TBL_CONFIGURATION_MODEL cm LEFT OUTER JOIN
     dbo.SRT_ItemNumbers i
     ON cmL.con_model = i.ITEM_NUMBER LEFT OUTER JOIN
     dbo.SRT_Data s
     ON i.ID = s.ITEM_NUMBER_ID
WHERE cm.con_model = 'model' 
ORDER BY s.prod_month DESC;

Upvotes: 1

Related Questions