Microsoft SQL Server: How-to realize a SQL Statement that only returns the maximum or the minimum rows of a group (similar to an non-existing aggregate function for the GROUP BY clause)

Actual situation

A change request (field „pcr“) only can have one unique assigned user that shall be informed about the assignment every night.

The assigned user (field „created_by“) of a change requests (field „pcr“) is changing several times a day. Every time the user (field „created_by“) changes to another user, a table entry with the actual time stamp (field „created_on“) is inserted to a SQL table.

At the end of the day, the system shall report via email the users that have been assigned to a PCR (only one user per change request).

The following table contains entries with people wo shall be informed every evening.

idpcrcreated_oncreated_by
1PCR-12020-02-26 09:20:52.977user1
2PCR-12020-02-26 09:20:54.667user1
3PCR-22020-02-26 09:20:57.137user1
4PCR-22020-02-26 09:20:59.003user1
5PCR-22020-02-26 09:21:07.540user3
6PCR-32020-02-26 09:21:09.850user3
7PCR-42020-02-26 09:21:12.850user3
8PCR-42020-02-26 09:21:15.100user3
9PCR-52020-02-26 09:21:55.323 user3
10PCR-22020-02-26 09:22:06.037user2
11PCR-52020-02-26 09:22:07.037user2
12PCR-72020-02-26 09:22:08.760 user2
13PCR-72020-02-26 10:20:07.233user2
14PCR-82020-02-26 10:20:08.967user2
15PCR-82020-02-26 10:20:13.393user1
16PCR-92020-02-26 10:20:15.020user1
17PCR-72020-02-26 10:20:19.497user3
18PCR-92020-02-26 10:20:20.163user3

A nightly executed batch job (at 23:59h every evening) shall provide the possibility to inform user1, user2 or user3 about their assigned PCR’s. Only the latest entry per PCR, that can be recognized by the timestamp (field „created_on“) represent the user assigned user at the end of the day

Problem

The SQL Statement shall output the latest entry per assigned user of each PCR. With a standard aggregate function this is not possible because the whole row entry has to be retrieved.

Approach

The Microsoft SQL Server has a proprietary function RANK(), that prints out the ranking of the PCR entry in connection with PARTITION BY and ORDER BY. The ORDER BY has to be used DESC, so that the latest entry always is on rank 1.

Because this methods only can be used within the SELECT statements fields and not withing WHERE, we need a second parent query to select everything with rank 1.

Solution

SELECT *
FROM (
	SELECT	id, 
			pcr,
			created_on, 
			created_by,
			RANK () OVER ( 
				PARTITION BY pcr
				ORDER BY created_on DESC
			) as biggestHasRangOne
	FROM nightly_email
)tab 
WHERE biggestHasRangOne=1 
AND CONVERT(date, GETDATE())=CONVERT (date, created_on)
Only the latest PCR assignment has been selected for that day.

To get the smallest one (minimum) in for that day you have to use

ORDER BY created_on ASC

In the RANK() – Partition by -Syntax.

Über Björn Karpenstein

Diplom Informatiker, Programmierer, Musikbegeisterter
Dieser Beitrag wurde unter MS SQL Server, Programmierung, SQL abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.