"The latest X of every Y" is a classic SQL problem that is frequently encountered at work. Of course, it is not as simple as "querying the latest X value by Y Group". It requires the record or primary key ID of the latest X. You can use an SQL statement to solve this problem. Generate instance tables and data:
"The latest X of every Y" is a classic SQL problem that is frequently encountered at work. Of course, it is not as simple as "querying the latest X value by Y Group". It requires the record or primary key ID of the latest X. You can use an SQL statement to solve this problem. Generate instance tables and data:
The Code is as follows:
/****** Create a table ******/
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [Table] ') and OBJECTPROPERTY (id, n'isusertable') = 1)
Drop table [dbo]. [Table]
GO
Create TABLE [dbo]. [Table] (
[ID] [int] IDENTITY (1, 1) not null,
[Y] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS not null,
[X] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
-- Insert data
Insert INTO [Table] (Y, X) values ('ccc ', '2017-02-02 02:02:02 ')
Insert INTO [Table] (Y, X) values ('aaa', '2017-02-02 02:02:02 ')
Insert INTO [Table] (Y, X) values ('aaa', '2017-03-03 03:03:03 ')
Insert INTO [Table] (Y, X) values ('bbb ', '2017-01-01 01:01:01 ')
Insert INTO [Table] (Y, X) values ('bbb ', '2017-02-02 02:02:02 ')
Insert INTO [Table] (Y, X) values ('bbb ', '2017-03-03 03:03:03 ')
Insert INTO [Table] (Y, X) values ('ccc ', '2017-01-01 01:01:01 ')
Insert INTO [Table] (Y, X) values ('aaa', '2017-01-01 01:01:01 ')
Insert INTO [Table] (Y, X) values ('ccc ', '2017-03-03 03:03:03 ')
Insert INTO [Table] (Y, X) values ('ddd ', '2017-01-01 01:01:01 ')
Insert INTO [Table] (Y, X) values ('ddd ', '2017-02-02 02:02:02 ')
Insert INTO [Table] (Y, X) values ('ddd ', '2017-03-03 03:03:03 ')
Insert INTO [Table] (Y, X) values ('Eee ', '2017-01-01 01:01:01 ')
Insert INTO [Table] (Y, X) values ('Eee ', '2017-02-02 02:02:02 ')
Insert INTO [Table] (Y, X) values ('Eee ', '2017-03-03 03:03:03 ')
GO
/****** Create a table ******/
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [Table] ') and OBJECTPROPERTY (id, n'isusertable') = 1)
Drop table [dbo]. [Table]
GO
Create TABLE [dbo]. [Table] (
[ID] [int] IDENTITY (1, 1) not null,
[Y] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS not null,
[X] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
-- Insert data
Insert INTO [Table] (Y, X) values ('ccc ', '2017-02-02 02:02:02 ')
Insert INTO [Table] (Y, X) values ('aaa', '2017-02-02 02:02:02 ')
Insert INTO [Table] (Y, X) values ('aaa', '2017-03-03 03:03:03 ')
Insert INTO [Table] (Y, X) values ('bbb ', '2017-01-01 01:01:01 ')
Insert INTO [Table] (Y, X) values ('bbb ', '2017-02-02 02:02:02 ')
Insert INTO [Table] (Y, X) values ('bbb ', '2017-03-03 03:03:03 ')
Insert INTO [Table] (Y, X) values ('ccc ', '2017-01-01 01:01:01 ')
Insert INTO [Table] (Y, X) values ('aaa', '2017-01-01 01:01:01 ')
Insert INTO [Table] (Y, X) values ('ccc ', '2017-03-03 03:03:03 ')
Insert INTO [Table] (Y, X) values ('ddd ', '2017-01-01 01:01:01 ')
Insert INTO [Table] (Y, X) values ('ddd ', '2017-02-02 02:02:02 ')
Insert INTO [Table] (Y, X) values ('ddd ', '2017-03-03 03:03:03 ')
Insert INTO [Table] (Y, X) values ('Eee ', '2017-01-01 01:01:01 ')
Insert INTO [Table] (Y, X) values ('Eee ', '2017-02-02 02:02:02 ')
Insert INTO [Table] (Y, X) values ('Eee ', '2017-03-03 03:03:03 ')
GO solves the classic SQL problem of "The latest X of every Y": The following methods are really amazing
The Code is as follows:
Select ID, Y, X
FROM [Table] T1
Where (NOT EXISTS
(Select 1
FROM [Table] T2
Where (T2.Y = T1. Y) AND (T2.X> T1. X or
T2.X = T1. x and T2.ID> T1. ID )))
/*************************************** **************************************/
Select *
FROM [Table]
Where ID IN
(Select MAX (T1.ID)
FROM [Table] T1 JOIN
(Select y, MAX (x) x
FROM [Table]
Group by y) T2 ON T1.Y = T2.Y AND T1.x = T2.x
Group by T1.y)
/*************************************** **************************************/
Select T. ID, T. Y, T. X
FROM [Table] T INNER JOIN
(Select MAX (T1.ID) AS ID
FROM [Table] T1 JOIN
(Select y, MAX (x) x
FROM [Table]
Group by y) T2 ON T1.Y = T2.Y AND T1.x = T2.x
Group by T1.y) T2 on t. ID = T2.ID
/*************************************** **************************************/
Select *
FROM [Table] T1
Where ID IN
(Select TOP 1 ID
FROM [Table]
Where Y = T1.Y
OrDER by x desc)
/*************************************** **************************************/
Select *
FROM [Table] T1
Where (ID =
(Select TOP 1 ID
FROM [Table]
Where Y = T1.Y
OrDER by x desc, id desc ))
/*************************************** **************************************/
/*************************************** **************************************/
Select ID, Y, X
FROM [Table] T1
Where (NOT EXISTS
(Select 1
FROM [Table] T2
Where (T2.Y = T1. Y) AND (T2.X> T1. X or
T2.X = T1. x and T2.ID> T1. ID )))
/*************************************** **************************************/
Select *
FROM [Table]
Where ID IN
(Select MAX (T1.ID)
FROM [Table] T1 JOIN
(Select y, MAX (x) x
FROM [Table]
Group by y) T2 ON T1.Y = T2.Y AND T1.x = T2.x
Group by T1.y)
/*************************************** **************************************/
Select T. ID, T. Y, T. X
FROM [Table] T INNER JOIN
(Select MAX (T1.ID) AS ID
FROM [Table] T1 JOIN
(Select y, MAX (x) x
FROM [Table]
Group by y) T2 ON T1.Y = T2.Y AND T1.x = T2.x
Group by T1.y) T2 on t. ID = T2.ID
/*************************************** **************************************/
Select *
FROM [Table] T1
Where ID IN
(Select TOP 1 ID
FROM [Table]
Where Y = T1.Y
OrDER by x desc)
/*************************************** **************************************/
Select *
FROM [Table] T1
Where (ID =
(Select TOP 1 ID
FROM [Table]
Where Y = T1.Y
OrDER by x desc, id desc ))
/*************************************** **************************************
/Efficiency. The indexing speed varies with different fields.
The speed of the first method is good in all aspects, and the indexing of column Y can greatly optimize the query speed.
