472,967 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,967 software developers and data experts.

Getting result with maximum date (Top 5)

dbrewerton
115 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.  

4 2796
SioSio
272 256MB
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
115 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
SioSio
272 256MB
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
SioSio
272 256MB
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

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

Similar topics

2
by: MX1 | last post by:
Hi, I have a VERY LONG, VERY COMPLEX query with about 1300 rows of data (e.g. custFirst, custLast, custAddress1, custAddress2, custCity, custState, custZip, custBalanceDue). Is there a way to...
7
by: Drygast | last post by:
I have a form where I would like to get the todays date and when the date is 15 or less I would like to assign a textfield the last date of previous month and when the date is larger then 15 I...
1
by: itsolutionsfree | last post by:
Hi All, i am deep trouble.so,please help me how to get minimum and maximum dates for particular Month and Year.Any kind of help will help. for ex: im passing : March 2006 Minimum Date -...
1
by: Eric | last post by:
In the subform user enters the dates. I need help when he is done with enteries of multiple rows program check the dates and the date which is max shows into another textbox name maxdate which is...
2
by: crackerbox | last post by:
I have a table that I need to find the maximum date per Doc_No per Parent_Doccategory. I can get the maximum date per Doc_No but I can't get the information for the next level. The following script...
3
kiss07
by: kiss07 | last post by:
Dear friend, I have two tables are there following below: table a table b --------- ------------ ...
4
by: sharmilah | last post by:
Hi all I want to save the current date in a database field in the following format dd/mm/yyyy for example 18/06/2007. How can i do this. The following is code I've used to assign nulls to my...
3
by: ren07 | last post by:
haii i need to get the time from the date time stamp which is stored in the database(SQL).............. i am getting the date time stamp like this..01-Jan-2000 08:00:00 i want it...
10
by: janieavis | last post by:
If I run this query I can see there are 2 records with the dates 10 August 2009 and 24 August 2009: SELECT dbo_JobMain.BpaMainPKey, dbo_JobMain.JobDefinitionPKey, dbo_JobMain.Value,...
5
by: hassanhundal007 | last post by:
In my table the field of DateTime name is TIN. In this field DATE and TIME are shown for date I pass this query: SELECT DAY(TIN) FROM SHIFT WHERE SHID = 1 Then the result is "10". So the same I...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.