By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,559 Online
Bytes IT Community

Home Questions Articles Browse Topics Latest Top Members FAQ

home > topics > microsoft sql server > questions > getting result with maximum date (top 5)
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

Getting result with maximum date (Top 5)

dbrewerton
100+
P: 112
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!
1 Week Ago #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.  

Share this Question
Share on Google+
4 Replies
P: 51
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.  
1 Week Ago #2
dbrewerton
100+
P: 112
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.
1 Week Ago #3
P: 51
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.  
1 Week Ago #4
P: 51
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.  
1 Week Ago #5

Post your reply

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

Question stats

  • viewed: 433
  • replies: 4
  • date asked: 1 Week Ago
BYTES.COM © 2020 About Bytes | Advertise on Bytes | Privacy Policy | Manage Cookies
Sitemap | Microsoft SQL Server Answers Sitemap | Microsoft SQL Server Insights Sitemap

Follow us to get the Latest Bytes Updates