How to get the row that holds the last value in a queue of identical values? (SQL)

Question

I think it's easier to show you an image:

So, for each fld_call_id, go to the next value, if it's identical. When we get to the last value, I need the value in column fld_menu_id.

Or, to put it in another way, eliminate fld_call_id duplicates and save only the last one.

Answer 1

You can use ROW_NUMBER:

WITH CTE AS( 
    SELECT RN = ROW_NUMBER() OVER (PARTITION BY fld_call_id ORDER BY fld_id DESC),
           fld_menu_id
    FROM dbo.TableName
)
SELECT fld_menu_id FROM CTE WHERE RN = 1

Answer 2

You can create a Rank column and only select that row, something along the lines of the following:

;WITH cte AS
(
 SELECT
  *
  ,RANK() OVER (PARTITION BY fld_call_id ORDER BY fld_id DESC) Rnk
 FROM YourTable
)
SELECT
 *
FROM cte
WHERE Rnk=1

So you GROUP BY fld_call_id and ORDER BY fld_id in descending order so that the last value comes first. These are the rows where Rnk=1.

Edit after comments of OP.

Answer 3

SELECT Table.*
FROM Table
INNER JOIN
(
    SELECT MAX(fldMenuID) AS fldMenuID,
    fldCallID
    FROM Table
    GROUP BY fldCallID
) maxValues
ON (maxValues.fldMenuID = Table.fldMenuID
AND maxValues.fldCallID= Table.fldCallID)

Answer 4

Hope This works

SELECT  A.*
FROM    table A
JOIN    (SELECT fld_id,
                ROW_NUMBER() OVER (PARTITION BY Fld_call_id ORDER BY fld_id DESC) [Row]
        FROM    table) LU ON A.fld_id = LU.fld_id
WHERE   LU.[Row] = 1