Copy Code code as follows:
/****** CREATE 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] (m) COLLATE chinese_prc_ci_as not NULL,
[X] [smalldatetime] not NULL
) on [PRIMARY]
Go
--Inserting data
Insert into [Table] (Y, X) VALUES (' CCC ', ' 2007-02-02 02:02:02 ')
Insert into [Table] (Y, X) VALUES (' AAA ', ' 2007-02-02 02:02:02 ')
Insert into [Table] (Y, X) VALUES (' AAA ', ' 2007-03-03 03:03:03 ')
Insert into [Table] (Y, X) VALUES (' BBB ', ' 2007-01-01 01:01:01 ')
Insert into [Table] (Y, X) VALUES (' BBB ', ' 2007-02-02 02:02:02 ')
Insert into [Table] (Y, X) VALUES (' BBB ', ' 2007-03-03 03:03:03 ')
Insert into [Table] (Y, X) VALUES (' CCC ', ' 2007-01-01 01:01:01 ')
Insert into [Table] (Y, X) VALUES (' AAA ', ' 2007-01-01 01:01:01 ')
Insert into [Table] (Y, X) VALUES (' CCC ', ' 2007-03-03 03:03:03 ')
Insert into [Table] (Y, X) VALUES (' DDD ', ' 2007-01-01 01:01:01 ')
Insert into [Table] (Y, X) VALUES (' DDD ', ' 2007-02-02 02:02:02 ')
Insert into [Table] (Y, X) VALUES (' DDD ', ' 2007-03-03 03:03:03 ')
Insert into [Table] (Y, X) VALUES (' EEE ', ' 2007-01-01 01:01:01 ')
Insert into [Table] (Y, X) VALUES (' EEE ', ' 2007-02-02 02:02:02 ')
Insert into [Table] (Y, X) VALUES (' EEE ', ' 2007-03-03 03:03:03 ')
Go
/****** CREATE 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] (m) COLLATE chinese_prc_ci_as not NULL,
[X] [smalldatetime] not NULL
) on [PRIMARY]
Go
--Inserting data
Insert into [Table] (Y, X) VALUES (' CCC ', ' 2007-02-02 02:02:02 ')
Insert into [Table] (Y, X) VALUES (' AAA ', ' 2007-02-02 02:02:02 ')
Insert into [Table] (Y, X) VALUES (' AAA ', ' 2007-03-03 03:03:03 ')
Insert into [Table] (Y, X) VALUES (' BBB ', ' 2007-01-01 01:01:01 ')
Insert into [Table] (Y, X) VALUES (' BBB ', ' 2007-02-02 02:02:02 ')
Insert into [Table] (Y, X) VALUES (' BBB ', ' 2007-03-03 03:03:03 ')
Insert into [Table] (Y, X) VALUES (' CCC ', ' 2007-01-01 01:01:01 ')
Insert into [Table] (Y, X) VALUES (' AAA ', ' 2007-01-01 01:01:01 ')
Insert into [Table] (Y, X) VALUES (' CCC ', ' 2007-03-03 03:03:03 ')
Insert into [Table] (Y, X) VALUES (' DDD ', ' 2007-01-01 01:01:01 ')
Insert into [Table] (Y, X) VALUES (' DDD ', ' 2007-02-02 02:02:02 ')
Insert into [Table] (Y, X) VALUES (' DDD ', ' 2007-03-03 03:03:03 ')
Insert into [Table] (Y, X) VALUES (' EEE ', ' 2007-01-01 01:01:01 ')
Insert into [Table] (Y, X) VALUES (' EEE ', ' 2007-02-02 02:02:02 ')
Insert into [Table] (Y, X) VALUES (' EEE ', ' 2007-03-03 03:03:03 ')
Go resolves "the latest x for every Y" classic SQL question: Here are a few ways to eight Immortals crossing
Copy Code code 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 speed of indexing in different fields is not the same, users differ.
The first method is good in every way, and in the Y column is indexed, you can greatly optimize the speed of the query.
