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!
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))
-
4 2796
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)
-
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.
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)
-
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))
-
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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 -...
|
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...
|
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...
|
by: kiss07 |
last post by:
Dear friend,
I have two tables are there following below:
table a table b
--------- ------------
...
|
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...
|
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...
|
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,...
|
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...
|
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=()=>{
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
| |