I have the following table:

Group    RecDate                oData
---------------------------------------
123      2022-03-20 02:00:00    F1xR
123      2022-03-21 02:30:00    F1xF
123      2022-03-22 05:00:00    F1xN
123      2022-03-15 04:00:00    F2xR

From the table above, I want to get the MAX date group by 2 char from oData field. Then I wrote a query like this:

SELECT a.Group, MAX(a.RecDate) RecDate, LEFT(a.oData, 2) oDataNo
INTO #t1
FROM TableData a
GROUP BY a.Group, LEFT(a.oData, 2)

SELECT * FROM #t1

Then, the result should be:

Group    RecDate                 oDataNo
--------------------------------------------
123      2022-03-22 05:00:00     F1
123      2022-03-15 04:00:00     F2

From the result above (#t1), I want to join with the TableData to get the RIGHT character (1 digit) from oData field. So I INNER JOIN the #t1 with TableData. The JOIN field is RecDate. But it is strange that the result isn't what I want.

The query like:

SELECT RIGHT(a.oData,1) oDataStat, b.* 
FROM TableData a 
INNER JOIN #t1 b ON a.RecDate = b.RecDate

The wrong result like:

The result should be:

Group    RecDate                 oDataNo       oDataStat
-----------------------------------------------------------
123      2022-03-22 05:00:00     F1            N
123      2022-03-15 04:00:00     F2            R

Am I doing wrong approach?

Please advise. Really appreciated.

Thank you.

CodePudding user response：

The query you provided returns the data you desire. However its cleaner to do it in a single query e.g.

WITH cte AS (
    SELECT *
        , RIGHT(a.oData,1) oDataStat
        , ROW_NUMBER() OVER (PARTITION BY LEFT(a.oData, 2) ORDER BY RecDate DESC) rn
    FROM TableData a
)
SELECT [Group], RecDate, oData, oDataStat
FROM cte
WHERE rn = 1
ORDER BY RecDate;

returns:

Note: Your query as posted doesn't actually run due to not escaping [Group] - you should ensure everything you post has any errors removed first.