Query to retrieve Text data type column value based on MAX value in other column

Question

Hi

I have a table with following columns and data

FldID     |  Rev    |  Words

10257       2        Some text is present here
10257        3        I changed this text
10258        2        Some more text for another item
10258        3        Dummy text
10258        6        Some more dummy text
10259        2        Sample query text

Words is a text data type. I want to write a query to extract "Words" from the above table for a combination of FldID with MAX(Rev)

Means for the above table output should be -

FldID     |  Rev    |  Words

10257        3        I changed this text
10258        6        Some more dummy text
10259        2        Sample query text

Pls. suggest.

Thanks

Answer 1

;WITH Q as (
  SELECT MAX(Rev) as Rev, FldID
  FROM t
  GROUP BY FldID
)
SELECT t.Words, Q.FldID, Q.Rev
FROM t 
  INNER JOIN Q 
    ON t.FldID = Q.FldID
    AND t.Rev = Q.Rev

Answer 2

with c as(
  select FldId, Rev, Words
    , row_number() over (partitioned by FldId order by Rev desc) as rn
  from table)
select FldId, Rev, Words 
from c
where rn =1;

Answer 3

Select words from table where (FldId, rev) in (
Select FldId, max(rev)
from table
group by FldId);

Answer to comment: this is how I would do it. I always think about these from the inside out. So the first question I asked was "how do I get the max revision number for each FldId?" Then I write a query to give me that.

Select FldId, max(rev)
from table
group by FldId;

Once I know that, I need to find the words that go along with that rev. So I added the first line to grab the words that match up with the FldId, rev combination.

More generally, in complicated queries, I will think through the whole thing using a bunch of nested queries until I have the answer I want. Then I'll go back through and look for ways to simplify the query.

Answer 4

You could do that with a subquery like this:

SELECT
    t1.*
FROM TBL t1
INNER JOIN (
    SELECT
        FldId,
        MAX(REV) AS MAXREV
    FROM TBL
    GROUP BY
        FldId
) t2
    ON t2.FldId = t1.FldId
    AND t2.MAXREV = t1.REV

You could also do it with a CTE, like this:

;WITH CTE AS (
    SELECT
        FldId,
        REV,
        Words,
        ROW_NUMBER() OVER (PARTITION BY FldId ORDER BY REV DESC) AS RowNumber
    FROM TBL
)

SELECT
    FldId,
    REV,
    Words
FROM CTE
WHERE RowNumber = 1

Answer 5

select t.FldID,t.Rev,t.Words from @t t
join(
select FldID,maxRev=MAX(Rev) from @t
group by FldID)x
on t.FldID = x.FldID and t.Rev = x.maxRev
order by t.FldID