Sql Query IN clause limit 1 Record

Question

I have a query where I am using IN clause (for 2 columns, 251 and 252) to get records from tables. In ideal case, either 251 or 252 will have value, so it should work fine, but in some cases, some jobs may have both 251 or 252 value, but I need to only limit 1 record based on jobHistoryID (max value). With below query, I get sometimes 2 and if I use Top 1 in Left Join, it gets me null

SELECT 
    [Job].[JobID],
    [Job].[PriceEstimateNumber],
    [CustomerReadyDate].[JobHistoryDate] As [ConfirmLeadDate1],
    [CustomerReadyDate].[JobColumnID]
FROM
    [tblJob] Job
        ON [Job].[JobID] = [StepJob].[JobID]
        AND [Job].[SubmitTimestamp] > '2019-01-01'
    LEFT JOIN (
            SELECT 
                [JobHistory].[JobID],
                [JobHistory].[JobHistoryDate],
                [JobHistory].[JobColumnID],
                [JobHistory].[JobHistoryID],
            FROM [dbo].[tblJob_History] AS [JobHistory]
            INNER JOIN [dbo].[tblJob] AS [Job]
                ON [Job].[JobID] = [JobHistory].[JobID] 
            WHERE
                [JobHistory].[jobColumnID] IN (251,252)      
            ) AS [CustomerReadyDate]
            ON [CustomerReadyDate].[JobID] = [Job].[JobID]

WHERE
    [Job].[OrderType] = 'Job'
    AND [Job].LocationTypeID = 1

Answer 1

You can add a ROW_NUMBER() function based on the jobHistoryID

SELECT 
    [Job].[JobID],
    [Job].[PriceEstimateNumber],
    [CustomerReadyDate].[JobHistoryDate] As [ConfirmLeadDate1],
    [CustomerReadyDate].[JobColumnID]
FROM
    [tblJob] Job
        ON [Job].[JobID] = [StepJob].[JobID]
        AND [Job].[SubmitTimestamp] > '2019-01-01'
    LEFT JOIN (
            SELECT 
                [JobHistory].[JobID],
                [JobHistory].[JobHistoryDate],
                [JobHistory].[JobColumnID],
                [JobHistory].[JobHistoryID],
                ROW_NUMBER() OVER(ORDER BY [JobHistoryID] DESC) rn
            FROM [dbo].[tblJob_History] AS [JobHistory]
            INNER JOIN [dbo].[tblJob] AS [Job]
                ON [Job].[JobID] = [JobHistory].[JobID] 
            WHERE
                [JobHistory].[jobColumnID] IN (251,252)      
            ) AS [CustomerReadyDate]
            ON [CustomerReadyDate].[JobID] = [Job].[JobID]

WHERE
    [Job].[OrderType] = 'Job'
    AND [Job].LocationTypeID = 1
    AND [CustomerReadyDate].rn = 1

but i think you can optimize you query as the following:

SELECT * FROM (

    SELECT 
        [Job].[JobID],
        [Job].[PriceEstimateNumber],
        [JobHistory].[JobHistoryDate] As [ConfirmLeadDate1],
        [JobHistory].[JobColumnID]
        ROW_NUMBER() OVER(ORDER BY [JobHistoryID] DESC) rn
    FROM [dbo].[tblJob_History] AS [JobHistory]
    INNER JOIN [dbo].[tblJob] AS [Job]
        ON [Job].[JobID] = [JobHistory].[JobID] 
    WHERE [JobHistory].[jobColumnID] IN (251,252)   
    AND [Job].[OrderType] = 'Job'
    AND [Job].LocationTypeID = 1

) AS T1

WHERE T1.rn = 1