CODEWITHSUNDEEP
sqlsql-server
gary.t
gary.t

Reputation: 21

Get the latest record for every group

I wanted to do a select statement and get the latest record for every group of pos_code by row_num column. Please refer to the expected result in the below.

I have tried few functions like last_value and max() but these don't work. Please assist me on the select statement query.

Below is the view_table

 pos_code | dept_id | row_num
 10001    |    1    |    1
 10001    |    3    |    2
 10001    |    2    |    3
 10002    |    5    |    1
 10002    |    6    |    2

Expected result

 pos_code | dept_id | row_num
 10001    |    2    |    3
 10002    |    6    |    2

Upvotes: 1

Views: 49

Answers (2)

forpas
forpas

Reputation: 164089

You can use a correlated subquery in the WHERE clause which returns the maximum row_num for each pos_code:

select t.* from tablename t
where t.row_num = (select max(row_num) from tablename where pos_code = t.pos_code)

Upvotes: 1

Richard Hansell
Richard Hansell

Reputation: 5403

This should do the trick?

WITH cte AS (
    SELECT
        pos_code,
        dept_id,
        row_num,
        ROW_NUMBER() OVER (PARTITION BY pos_code ORDER BY row_num DESC) AS order_id
    FROM
        view_table)
SELECT
    pos_code,
    dept_id,
    row_num
FROM
    cte
WHERE
    order_id = 1;

Upvotes: 1

Related Questions