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!

[SioSio]

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

select * from Table_name as t1 where HighVulns=(select max(HighVulns) from Table_name where App=t1.App)
 

[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:

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
 

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.

[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.

select TOP 5 * from Table_name as t1 where HighVulns=(select max(HighVulns) from Table_name  where App=t1.App)
 

[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

SELECT TOP 5 * FROM Table_Name AS t1 WHERE NOT EXISTS (SELECT 1 FROM Table_Name AS t2 WHERE SUBSTRING(t1.App,1,4) = SUBSTRING(t2.App,1,4) AND t1.HighVulns < t2.HighVulns)
 

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.

SELECT TOP 5 * FROM Table_Name AS t1 WHERE HighVulns = (SELECT MAX(HighVulns) FROM Table_Name AS t2 WHERE SUBSTRING(t1.App,1,4) = SUBSTRING(t2.App,1,4))