{"id":3147,"date":"2020-02-26T11:56:15","date_gmt":"2020-02-26T10:56:15","guid":{"rendered":"http:\/\/www.capri-soft.de\/blog\/?p=3147"},"modified":"2020-02-26T12:34:45","modified_gmt":"2020-02-26T11:34:45","slug":"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","status":"publish","type":"post","link":"https:\/\/www.capri-soft.de\/blog\/?p=3147","title":{"rendered":"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)"},"content":{"rendered":"\n

Actual situation<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

id<\/th>pcr<\/th>created_on<\/th>created_by<\/th><\/tr><\/thead>
1<\/td>PCR-1<\/td>2020-02-26 09:20:52.977<\/td>user1<\/td><\/tr>
2<\/td>PCR-1<\/td>2020-02-26 09:20:54.667<\/td>user1<\/td><\/tr>
3<\/td>PCR-2<\/td>2020-02-26 09:20:57.137<\/td>user1<\/td><\/tr>
4<\/td>PCR-2<\/td>2020-02-26 09:20:59.003<\/td>user1<\/td><\/tr>
5<\/td>PCR-2<\/td>2020-02-26 09:21:07.540<\/td>user3<\/td><\/tr>
6<\/td>PCR-3<\/td>2020-02-26 09:21:09.850<\/td>user3<\/td><\/tr>
7<\/td>PCR-4<\/td>2020-02-26 09:21:12.850<\/td>user3<\/td><\/tr>
8<\/td>PCR-4<\/td>2020-02-26 09:21:15.100<\/td>user3<\/td><\/tr>
9<\/td>PCR-5<\/td>2020-02-26 09:21:55.323 <\/td>user3<\/td><\/tr>
10<\/td>PCR-2<\/td>2020-02-26 09:22:06.037<\/td>user2<\/td><\/tr>
11<\/td>PCR-5<\/td>2020-02-26 09:22:07.037<\/td>user2<\/td><\/tr>
12<\/td>PCR-7<\/td>2020-02-26 09:22:08.760 <\/td>user2<\/td><\/tr>
13<\/td>PCR-7<\/td>2020-02-26 10:20:07.233<\/td>user2<\/td><\/tr>
14<\/td>PCR-8<\/td>2020-02-26 10:20:08.967<\/td>user2<\/td><\/tr>
15<\/td>PCR-8<\/td>2020-02-26 10:20:13.393<\/td>user1<\/td><\/tr>
16<\/td>PCR-9<\/td>2020-02-26 10:20:15.020<\/td>user1<\/td><\/tr>
17<\/td>PCR-7<\/td>2020-02-26 10:20:19.497<\/td>user3<\/td><\/tr>
18<\/td>PCR-9<\/td>2020-02-26 10:20:20.163<\/td>user3<\/td><\/tr>
<\/td><\/td><\/td><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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<\/p>\n\n\n\n

Problem<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

Approach<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

Solution<\/h2>\n\n\n
\nSELECT *\nFROM (\n\tSELECT\tid, \n\t\t\tpcr,\n\t\t\tcreated_on, \n\t\t\tcreated_by,\n\t\t\tRANK () OVER ( \n\t\t\t\tPARTITION BY pcr\n\t\t\t\tORDER BY created_on DESC\n\t\t\t) as biggestHasRangOne\n\tFROM nightly_email\n)tab \nWHERE biggestHasRangOne=1 \nAND CONVERT(date, GETDATE())=CONVERT (date, created_on)\n<\/pre><\/div>\n\n\n
\"\"
Only the latest PCR assignment has been selected for that day.<\/figcaption><\/figure><\/div>\n\n\n\n
To get the smallest one (minimum) in for that day you have to use<\/p>\n\n\n\n
ORDER BY created_on ASC<\/pre>\n\n\n\n
In the RANK() – Partition by -Syntax.<\/p>\n