467,149 Members | 1,361 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,149 developers. It's quick & easy.

Getting result with maximum date (Top 5)

dbrewerton
100+
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

✓ answered by 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
Expand|Select|Wrap|Line Numbers
  1. 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)
  2.  
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.
Expand|Select|Wrap|Line Numbers
  1. 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))
  2.  

  • viewed: 1719
Share:
4 Replies
128KB
Are "App1-new" and "App1" the same group?
The following examples are treated as different groups.
Expand|Select|Wrap|Line Numbers
  1. select * from Table_name as t1 where HighVulns=(select max(HighVulns) from Table_name where App=t1.App)
  2.  
Jan 14 '20 #2
dbrewerton
100+
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:
Expand|Select|Wrap|Line Numbers
  1. App1-new | 1/12/2020 | ATeam | 500 | 100 | 800
  2. App1 | 1/11/2020 | ATeam | 500 | 100 | 764
  3. App2 | 1/08/2020 | ATeam | 500 | 100 | 600
  4. App3 | 1/07/2020 | ATeam | 500 | 100 | 300
  5. App4 | 1/05/2020 | ATeam | 500 | 100 | 250
  6. App4 | 1/04/2020 | ATeam | 500 | 100 | 225
  7. App5 | 1/08/2020 | ATeam | 500 | 100 | 190
  8.  
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
128KB
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.

Expand|Select|Wrap|Line Numbers
  1. select TOP 5 * from Table_name as t1 where HighVulns=(select max(HighVulns) from Table_name  where App=t1.App)
  2.  
Jan 14 '20 #4
128KB
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
Expand|Select|Wrap|Line Numbers
  1. 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)
  2.  
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.
Expand|Select|Wrap|Line Numbers
  1. 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))
  2.  
Jan 14 '20 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by MX1 | last post: by
7 posts views Thread by Drygast | last post: by
1 post views Thread by itsolutionsfree@gmail.com | last post: by
1 post views Thread by Eric | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.