MIN () gegen Partition in Gruppe in SQL server

Ich möchte MIN () gegen die Partition im SQL server

MY CODE DROP TABLE #TEMP_TABLE CREATE TABLE #TEMP_TABLE (SR_NO INT,DATA VARCHAR(5),DATE DATETIME) INSERT INTO #TEMP_TABLE (SR_NO,DATA,DATE) VALUES (1,'A','2015-11-25 18:37:59.120') INSERT INTO #TEMP_TABLE (SR_NO,DATA,DATE) VALUES (2,'A','2015-11-25 18:36:59.120') INSERT INTO #TEMP_TABLE (SR_NO,DATA,DATE) VALUES (3,'A','2015-11-25 18:35:59.120') INSERT INTO #TEMP_TABLE (SR_NO,DATA,DATE) VALUES (4,'B','2015-11-25 18:34:59.120') INSERT INTO #TEMP_TABLE (SR_NO,DATA,DATE) VALUES (5,'B','2015-11-25 18:33:59.120') INSERT INTO #TEMP_TABLE (SR_NO,DATA,DATE) VALUES (6,'B','2015-11-25 18:32:59.120') INSERT INTO #TEMP_TABLE (SR_NO,DATA,DATE) VALUES (7,'B','2015-11-25 18:31:59.120') INSERT INTO #TEMP_TABLE (SR_NO,DATA,DATE) VALUES (8,'B','2015-11-25 18:30:59.120') INSERT INTO #TEMP_TABLE (SR_NO,DATA,DATE) VALUES (9,'A','2015-11-25 18:27:59.120') INSERT INTO #TEMP_TABLE (SR_NO,DATA,DATE) VALUES (10,'B','2015-11-25 18:26:59.120') SELECT ROW_NUMBER() OVER (PARTITION BY DATA ORDER BY DATA DESC) AS ROW_NUM, DATA, MAX(DATE) AS MAX_DATE , MIN(DATE) AS MIN_DATE FROM #TEMP_TABLE GROUP BY DATA 

MEINER AUSGANG

Bildbeschreibung hier eingeben

ERWARTETE DATEN erwartete Ausgabe

Ich möchte min date als die Gruppe der Aufzeichnung als min date & max date. Bedeutet, sagen, erste Gruppe von datasätzen in den oben genannten data sind

 data time A 5:00 A 4:00 A 3:00 B 2:00 A 1:00 

dann möchte ich die Ausgabe als zurückgeben

 data max min a 5:00 3:00 

 SELECT MAX(DATA) AS DATA, MAX([DATE]) AS MAX_DATE, MIN([DATE]) AS MIN_DATE FROM ( SELECT *, val = ROW_NUMBER() OVER (ORDER BY [DATE] DESC) - ROW_NUMBER() OVER (PARTITION BY DATA ORDER BY [DATE] DESC) FROM #TEMP_TABLE ) t GROUP BY val 

Ausgabe –

 DATA MAX_DATE MIN_DATE ----- ----------------------- ----------------------- A 2015-11-25 15:53:12.940 2015-11-25 15:51:12.940 B 2015-11-25 15:50:12.940 2015-11-25 15:48:12.943 C 2015-11-25 15:47:12.943 2015-11-25 15:46:12.943 A 2015-11-25 15:43:12.943 2015-11-25 15:43:12.943 D 2015-11-25 15:45:12.943 2015-11-25 15:42:12.943 

aktualisieren –

 SELECT *, row_id = ROW_NUMBER() OVER (ORDER BY [DATE] DESC), row_id_by_data = ROW_NUMBER() OVER (PARTITION BY DATA ORDER BY [DATE] DESC), group_id = ROW_NUMBER() OVER (ORDER BY [DATE] DESC) - ROW_NUMBER() OVER (PARTITION BY DATA ORDER BY [DATE] DESC) FROM #TEMP_TABLE 

Ergebnis –

Bildbeschreibung hier eingeben

Wenn ich deine Frage richtig verstehe, denke ich, du suchst so etwas wie das, was ich hier habe. (Ich habe die Tischdeklaration geändert, weil ich nicht wollte, dass es herumhängt, nachdem ich das getestet habe)

 DECLARE @TEMP_TABLE TABLE (SR_NO INT,DATA VARCHAR(5),DT DATETIME) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (1,'A',(DATEADD(MINUTE,11,GETDATE()))) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (2,'A',(DATEADD(MINUTE,10,GETDATE()))) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (3,'A',(DATEADD(MINUTE,9,GETDATE()))) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (4,'B',(DATEADD(MINUTE,8,GETDATE()))) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (5,'B',(DATEADD(MINUTE,7,GETDATE()))) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (6,'B',(DATEADD(MINUTE,6,GETDATE()))) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (7,'C',(DATEADD(MINUTE,5,GETDATE()))) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (8,'C',(DATEADD(MINUTE,4,GETDATE()))) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (9,'D',(DATEADD(MINUTE,3,GETDATE()))) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (10,'D',(DATEADD(MINUTE,2,GETDATE()))) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (1,'A',(DATEADD(MINUTE,1,GETDATE()))) INSERT INTO @TEMP_TABLE (SR_NO,DATA,DT) VALUES (1,'B',GETDATE()) SELECT DATA, MAX(DT) as MaxDate, MIN(DT) as MinDate FROM @TEMP_TABLE GROUP BY DATA