There is a table with records in the history of the ticket.

It is necessary to make a sample, grouping users. For each user, take only one line (such that the date is maximum).

How to do it? I tried this:

CREATE TABLE [TICKETHISTORY]( [ID] varchar(10) NOT NULL, [TICKETID] varchar(10) NOT NULL, [FIELDNAME] [varchar](64) NULL, [NOTE] [varchar](255) NULL, [CREATEUSER] varchar(10) NULL, [CREATEDATE] [datetime] NULL, CONSTRAINT [PK_TICKETHISTORY] PRIMARY KEY CLUSTERED ( [ID] ASC ) ) ON [PRIMARY] INSERT INTO [TICKETHISTORY] ([ID],[TICKETID],[FIELDNAME],[NOTE],[CREATEUSER],[CREATEDATE]) VALUES('1','100','DEPART1','Notes1','User1', '2017-01-01') INSERT INTO [TICKETHISTORY] ([ID],[TICKETID],[FIELDNAME],[NOTE],[CREATEUSER],[CREATEDATE]) VALUES('2','100','DEPART1','Notes2','User1','2017-01-02') INSERT INTO [TICKETHISTORY] ([ID],[TICKETID],[FIELDNAME],[NOTE],[CREATEUSER],[CREATEDATE]) VALUES('3','100','DEPART2','Notes3','User1','2017-01-03') INSERT INTO [TICKETHISTORY] ([ID],[TICKETID],[FIELDNAME],[NOTE],[CREATEUSER],[CREATEDATE]) VALUES('4','101','DEPART1','Notes4','User2','2017-01-01') INSERT INTO [TICKETHISTORY] ([ID],[TICKETID],[FIELDNAME],[NOTE],[CREATEUSER],[CREATEDATE]) VALUES('5','101','DEPART2','Notes5','User2','2017-01-02')

Only lines 3 and 5 should remain in the sample:

 3 100 User1 2017-01-03 5 101 User2 2017-01-02

The request itself:

 SELECT [ID] ,[TICKETID] ,[CREATEUSER] ,MAX([CREATEDATE]) FROM [TICKETHISTORY] GROUP BY TICKETID

And I tried to list all the fields. BOL read https://msdn.microsoft.com/ru-ru/library/ms177673(v=sql.105) but did not find something suitable.

  • if you have 3 users, why leave 2 lines only? - Denis
  • oh true! old example copied - rejik
  • And why are you having no use? In which you can specify select date = max (date) - Kawalski
  • @Kawalski How to do it? Can you give an example? - rejik

2 answers 2

If you need to keep a record of only the latest date for each user, then you can use the connection with the table, where the date of the last record will be considered for each user, and cut off according to the conditions.

The logic is something like this:

 SELECT ID ,TICKETID ,CREATEUSER ,M.MAXDATE FROM TICKETHISTORY TH JOIN (SELECT CREATEUSER, MAX(CREATEDATE) as MAXDATE FROM TICKETHISTORY GROUP BY CREATEUSER ) M ON TH.CREATEUSER = M.CREATEUSER AND TH.CREATEDATE = M.MAXDATE
  • your request does not fully meet the requirements of the Author: it requires one line for each user, in your request several lines with the same creation date are allowed - Serge Nazarenko

Try this

 WITH CTE AS (SELECT [ID] ,[TICKETID] ,[CREATEUSER] ,[CREATEDATE] ,ROW_NUMBER() OVER (PARTITION BY [CREATEUSER] ORDER BY [CREATEDATE] DESC) as rw FROM [TICKETHISTORY] ) SELECT [ID] ,[TICKETID] ,[CREATEUSER] ,[CREATEDATE] FROM CTE WHERE rw <= 1

Or so

 SELECT [ID], [TICKETID], [CREATEUSER], MAX([CREATEDATE]) FROM [TICKETHISTORY] Group by [ID], [TICKETID], [CREATEUSER]