I have a table where I have my users' data.

There is a second table where I represent the versions where each of these users are:

I'm having trouble writing a query to return users where their current version is 1.3. If the user has already had version 1.3 at some point, but currently it is no longer in it, this value should not be selected. A user over time can transition through several versions. In the query, I only want users who are currently on version 1.3. That is, only users with Id: 2 and 7.

CodePudding user response：

You can use MAX with partitioning. Here I'm presuming that if a user is at a version higher than 1.3 then that user has already been at 1.3 so WHERE clause can return only those users at 1.3.

DROP TABLE IF EXISTS #userdata;
CREATE TABLE dbo.#userdata(
  [Id] INT,
  [Name] VARCHAR(100),
  [VersionId] INT
);
GO

INSERT INTO dbo.#userdata ([Id],[Name],[VersionId])
SELECT 1, 'Name A', 1 UNION ALL
SELECT 2, 'Name B', 2 UNION ALL
SELECT 3, 'Name C', 1 UNION ALL
SELECT 4, 'Name A', 2 UNION ALL
SELECT 5, 'Name E', 1 UNION ALL
SELECT 6, 'Name F', 3 UNION ALL
SELECT 7, 'Name G', 2 UNION ALL
SELECT 8, 'Name C', 4 UNION ALL
SELECT 9, 'Name A', 5;


DROP TABLE IF EXISTS #versiondata;
CREATE TABLE dbo.#versiondata(
  [Id] INT,
  [Version] DECIMAL(18, 6)
);
GO

INSERT INTO dbo.#versiondata ([Id],[Version])
SELECT 1, 1.2 UNION ALL
SELECT 2, 1.3 UNION ALL
SELECT 3, 1.4 UNION ALL
SELECT 4, 1.5 UNION ALL
SELECT 5, 1.6;

WITH mx AS (
    SELECT DISTINCT
        u.Name,
        MAX(v.Version) OVER (PARTITION BY u.Name) Version
    FROM #userdata u
    INNER JOIN #versiondata v ON v.id = u.VersionId
)
SELECT mx.Name, mx.Version
FROM mx
WHERE mx.Version = 1.3;

CodePudding user response：

Start with a subquery to find the "latest" version for each user. In this case "latest" is defined as the row with the largest id value for each user.

                  SELECT MAX(Id) Id
                    FROM usertable 
                   GROUP BY Name

Then use the subquery to fetch the rows you need from your usertable, join to your versiontable, and use an appropriate WHERE clause.

SELECT versiontable.Version, usertable.Name
  FROM versiontable
  JOIN usertable on versiontable.Id = usertable.VersionId
  JOIN ( 
                     SELECT MAX(Id) Id
                       FROM usertable 
                      GROUP BY Name
       ) m ON usertable.Id = m.Id
 WHERE Version = '1.3'

Fiddle here.

CodePudding user response：

You can use a windowing function returning the last entry per user like this

SELECT
    x.Id, x.Name
FROM (SELECT
          Id,
          Name,
          VersionId,
          ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Id DESC) AS rowNum
      FROM UserData) x
      INNER JOIN VersionData v
          ON x.VersionId = v.Id
WHERE
    x.rowNum = 1 AND
    v.Version = '1.3'

This works even if a user downgrades to a previous version.

Returns

See: http://sqlfiddle.com/#!18/93a572/15/0