CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Leo
Leo

Reputation: 908

How to fetch records with lastest 2 version numbers

How to fetch records with latest 2 version numbers from a table using SQL query. I want to fetch user ids for a tracking id for the last 2 versions.

Below is my table description:

TRACKING_ID ,
User_id,
Version_number

Below query gives me the user id having the latest version. 

Select user_id 
from table t1 
join 
(select tracking_id,max(version_number) as version_number 
from table 
 group by tracking_id ) t2
    on t1.tracking_id=t2.tracking_id
    and t1.version_number=t2.version_number

Appreciate your response.

Upvotes: 1

Views: 69

Answers (2)

xQbert
xQbert

Reputation: 35333

I don't fully understand the question but something like this may point you in the correct direction.

WITH CTE AS (
SELECT tracking_ID, user_ID, version_number,
Row_number() over (partition by USER_ID, Version_number desc) as RN)
SELECT * 
FROM cte 
WHERE RN <= 2;

I'm not sure if the user_ID or the tracking_ID needs to be partitioned...

Upvotes: 1

Lamak
Lamak

Reputation: 70648

You can use ROW_NUMBER:

SELECT  tracking_id,
        user_id,
        version_number
FROM (  SELECT  *,
                ROW_NUMBER() OVER(PARTITION BY tracking_id ORDER BY version_number DESC) AS RN
        FROM YourTable) AS T
WHERE RN <= 2

Upvotes: 3

Related Questions