Getting result with maximum date (Top 5)

dbrewerton

Hello, I'm trying to figure something out for my query and could really use some help. I'm trying to get the top 5 results based on latest date. The issue I'm running into is trying to only get one result per application by maximum High Vuln Count. Here is my example data set.

App | Last_Scan | Team | LOC | RiskLevel | HighVulns
----------------------------------------------------------
App1-new | 1/12/2020 | ATeam | 500 | 100 | 800
App1 | 1/11/2020 | ATeam | 500 | 100 | 764
App2 | 1/08/2020 | ATeam | 500 | 100 | 600
App3 | 1/07/2020 | ATeam | 500 | 100 | 300
App4 | 1/05/2020 | ATeam | 500 | 100 | 250
App4 | 1/04/2020 | ATeam | 500 | 100 | 225
App5 | 1/08/2020 | ATeam | 500 | 100 | 190

What I'm trying to get:

App | Last_Scan | Team | LOC | RiskLevel | HighVulns
----------------------------------------------------------
App1-new | 1/12/2020 | ATeam | 500 | 100 | 800
App2 | 1/08/2020 | ATeam | 500 | 100 | 600
App3 | 1/07/2020 | ATeam | 500 | 100 | 300
App4 | 1/05/2020 | ATeam | 500 | 100 | 250
App5 | 1/08/2020 | ATeam | 500 | 100 | 190

Essentially, my two key columns to queue off of are Last_Scan and HighVulns. If the Last_Scan and HighVulns are higher, strip out the values that are lower. Any ideas? Thank you!

Jan 13 '20 #1

SioSio

Are "App1-new" and "App1" the same group?
The following examples are treated as different groups.

Jan 14 '20 #2

dbrewerton

Hi and thanks for getting back. Ok, I gave that a try but the result is still including the duplicates. So, say I have two apps, both are the same type of application however, the one with the higher count is the one I want if the date is more recent. Recall this is the data from the query:
So what I would expect for the top five is the following apps with dates:
App1-new | 1/12/2020
App2 | 1/8/2020
App3 | 1/7/2020
App4 | 1/5/2020
App5 | 1/8/2020

So it would strip out the results for app1 and app4 from 1/4/20.

Jan 14 '20 #3

SioSio

To get only the top 5 items, use "TOP".
Looking at the results you have shown, App1 and App1-new appear to be the same App.
A rule is needed to determine that APP1 and APP1-new are the same, but the example below does not include a way to assume that they are the same.

For example, cut out a character string using the "SUBSTRING" or "RIGHT", "LEFT" function.

Jan 14 '20 #4

SioSio

Let's change the way of thinking. If the concept of “maximum” is considered as “there is no larger record than the relevant record”, then the following can be written.
And example are grouped by 4 characters from the left of the App
As another method, in order to find the record where HighVulns is the largest in the group, a search is performed by finding the largest HighVulns in the group by a subquery.

Jan 14 '20 #5