Simplify SQL select statement

Question

I have a table with such a structure

CREATE TABLE UsersHistory ( Id INT IDENTITY, UserID INT, StatusId INT, CreateTime DATETIME, ChangedTime DATETIME ) INSERT INTO UsersHistory(UserID, StatusId, CreateTime, ChangedTime) SELECT 1,1,'20150414','20150414' UNION ALL SELECT 1,2,'20150414','20150415' UNION ALL SELECT 1,3,'20150414','20150416' UNION ALL SELECT 2,1,'20150413','20150413' UNION ALL SELECT 2,3,'20150413','20150416' 

and request

 ;WITH k AS ( SELECT uh.UserID,MAX(uh.ChangedTime) AS Dt FROM UsersHistory AS uh WHERE uh.ChangedTime<'20150416' GROUP BY uh.UserID ) SELECT k.UserID,uh.StatusId FROM k INNER JOIN UsersHistory AS uh ON k.UserID = uh.UserID AND k.Dt = uh.ChangedTime 

The request is too simple and needs no further explanation. I want to simplify this. (Delete a connection with a date and time type column).

Any suggestion?

Answer 1

You can use ROW_NUMBER() with PARTITION to achieve this. Something like that

 ;WITH CTE as ( SELECT UserID, StatusId, CreateTime, ChangedTime,ROW_NUMBER()OVER(PARTITION BY UserID ORDER BY ChangedTime DESC) r FROM UsersHistory WHERE ChangedTime < '20150416' ) SELECT UserID, StatusId FROM CTE WHERE r = 1 

Answer 2

In SQL Server 2012+, you can use first_value() :

 SELECT uh.UserID, FIRST_VALUE(uh.StatusId) OVER (PARTITION BY uh.UserId ORDER BY ChangedTIme DESC) MAX(uh.ChangedTime) AS Dt FROM UsersHistory AS uh WHERE uh.ChangedTime < '20150416'; 

Answer 3

As far as I understand, you want to select UserId and StatusId from the records with the maximum value of ChangedTime, where ChangedTime is not equal to "20150416". Try this query:

 SELECT uh.UserId, uh.StatusId FROM UsersHistory AS uh WHERE uh.ChangedTime<'20150416' AND uh.ChangedTime=(SELECT MAX(ChangedTime) FROM UsersHistory WHERE UserId=uh.UserId); 

Answer 4

You can add a subquery to the WHERE clause. Something like that:

 SELECT uh.UserID, MAX(uh.ChangedTime) AS Dt, uh.StatusId FROM UsersHistory uh WHERE uh.ChangedTime < '20150416' AND uh.ChangedTime= ( SELECT MAX(ChangedTime) FROM UsersHistory WHERE UserId=uh.UserId ) GROUP BY uh.UserID, uh.StatusId 

Answer 5

With APPLY :

 Select uh1.UserID, oa.StatusID From UserHistory uh1 Cross Apply(Select * From (Select Top 1 uh2.StatusID, uh2.Changetime From UserHistory uh2 Where uh2.UserID = uh1.UserID And uh2.Changetime < '20150416' Order By uh2.Changetime desc) t where t.Changetime = uh1.Changetime) oa 

Answer 6

 SELECT distinct uh.UserID, statusid FROM UsersHistory uh inner join (select UserID, max(ChangedTime) dt from UsersHistory group by UserID) ct on (ct.UserID=uh.userid and ct.dt=uh.ChangedTime)