473,836 Members | 1,438 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SELECT Record From Group With Max Value

72 New Member
Hi guys

I am trying to write a particular query for a database that records different issues that affect a company but its not working quite right.

The main fields include approx 10 different config items of issues, the issue description itself and a score depending on the impact/severity.

What I want is to show the highest score per config item, which will return 10 rows, one for each config. I need the issue description to correspond with the high score.

I tried using 'group by' on the config, 'max' on the score and 'first' with the issue description which returns 10 rows however the issue does not correspond with the score. I have tried making it 'last' and also others however its not working right.

Any ideas if there is a way to do this?

Aug 13 '11
18 7886
Gareth Jones
72 New Member
Right. I have primary key called [ID] and its an autonumber.

How can I link this to the [issue description]? I have tried a number of ways but cant seem to get this one.
Aug 15 '11 #11
32,584 Recognized Expert Moderator MVP
This is more complicated than the very basic as is handles duplicates (Assuming that any of the possible records qualify as well as any other - If that's not a reliable assumption that should have been made clear in the question.) :
Expand|Select|Wrap|Line Numbers
  1. SELECT   qryF2.[Config ID]
  2.        , subQ.[CS Impact Score])
  3.        , subQ.[Issue Description]
  4. FROM     [form2query] AS qryF2
  5.          INNER JOIN
  6.          (SELECT [Config ID]
  7.                , [CS Impact Score]
  8.                , [Issue Description]
  9.          ) AS subQ
  10.   ON     qryF2.[Config ID] = subQ.[Config ID]
  11. HAVING   (Max(qryF2.[CS Impact Score]) = subQ.[CS Impact Score])
  12. GROUP BY [Config ID]
You may notice that I didn't use the [ID] field after all. There is a way to do it that way, but I discovered a way that allows (more easily) the showing of more fields related to the record where the maximum score is found. I rarely need to use this approach myself so i'm somewhat rusty in the finer details, but I think what I've suggested should work nicely for you.
Aug 15 '11 #12
32,584 Recognized Expert Moderator MVP
I just reread what I posted and noticed this method doesn't handle duplicates well. It will give multiple output records if multiple records are found with the same maximum score. Unfortunately, this seems to be the only approach I can find for now that handles Memo fields. Memo fields are severely restrictive when it comes to queries. Essentially they are unable to be manipulated in any way. Treated as vanilla data they can work, but they cannot be used with any functions as the functions all seem to expect standard string data. The [ID] approach I considered originally would have needed to compare the [ID] fields and use only the [Issue Description] value from the matching record. This involves function calls and none can handle the Memo field.
Aug 15 '11 #13
Gareth Jones
72 New Member
OK. I am getting an error 'Syntax error in HAVING clause' now

I have jigged around with the code and get a number of dfferent errors, however I cannot seem to get this one working :(

Really appreciate your help this far...
Aug 16 '11 #14
32,584 Recognized Expert Moderator MVP
Exactly which SQL were you using when you got the reported error? IE. Was this error received before making any changes to the SQL? If not then I need to see exactly what you were using.

PS. I have found the same problem and I'm working on a viable solution for you. I haven't done it this way for so long I'm struggling to remember/work out how it should work, but I'll post back when I've got my brain working properly and found a solution.
Aug 16 '11 #15
Gareth Jones
72 New Member
Yes the error above was using the code you gave me.
Aug 16 '11 #16
32,584 Recognized Expert Moderator MVP
I was working for ages on the assumption I needed a three level sub-query, when I stumbled over the solution after two levels. It's not as complex as I'd been expecting, but it seems to work ok. Try it out and see how you get on :
Expand|Select|Wrap|Line Numbers
  1. SELECT qF2.*
  2. FROM   [Form2Query] AS qF2
  3.        INNER JOIN
  4.        (SELECT   [Config ID]
  5.                , Max([CS Impact Score]) AS [MaxCSImpactScore]
  6.         FROM     [Form2Query]
  7.         GROUP BY [Config ID]
  8.        ) AS sQ
  9.   ON   (qF2.[Config ID] = sQ.[Config ID])
  10.  AND   (qF2.[CS Impact Score] = sQ.[MaxCSImpactScore])
Aug 16 '11 #17
Gareth Jones
72 New Member
Thats it! Works perfectly. Thanks for all your help.
Aug 16 '11 #18
32,584 Recognized Expert Moderator MVP
No worries Gareth. Pleased to hear that worked for you.

BTW, I noticed a small overcomplicatio n I'd left in before and removed it from the earlier post. It's effect should be the same but the code's tidier is all.
Aug 16 '11 #19

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

Similar topics

by: Jimmy Tran | last post by:
Hello, I am doing a bmw tracking project for school using asp and access2000. on my search.asp page, I can search for a particular bmw and order it if i want. It works fine when i do select query to get data. But when I try to order a bmw(using update query), it gives me this message error: Error Type: Microsoft JET Database Engine (0x80004005) Operation must use an updateable query. /bmw/search.asp, line 257
by: GL | last post by:
Hi, Is there a way to add a field to an existing table using a query of some sort (without needing to manually add a field to the table). I know how to do it with a make table query, but I have a specific need to only add a new field to a table if possible. Here's a simplified example of what I'm trying to do: I get a file with the following two fields: First Name
by: Don Wash | last post by:
Hi All! I've been searching everywhere for a simple sample of producing a bar graph using CrystalReport by specifying SQL Query, and I've found none of it! I find so many complex samples with so many features buried in the sample but all I want to know is a very simple thing, how to create bar charts with CrystalReport in VS.NET using SQL Query. Could anyone provide me a sample showing how to produce a simple bar chart using SQL Query...
by: Eduardo78 | last post by:
Ok, I have a Field that has a combination of letter C and a Number that increments by one. example: C1 C2 C3 C4 C5 C6
by: Jim Lewis | last post by:
I have read several things that state accessing a Web Service through a Query String should work. However, when I try to execute http://localhost/webservice1/service1.asmx/HelloWorld I get the error below. Can I access a Web Service through a Query Sting. I need to send XML to a Flash movie using a Web Service. Thank You, Jim Lewis Server Error in '/WebService1' Application.
by: astersathya | last post by:
Date Format in MS Access using a query Hi I want to alter the existing column's format using the alter query. The issue I am facing is I want to set the default date format of an existing "Date_Edit" column in "DD/MM/YYYY" format irrespective of Date format of the Operating System. It would also be really appreciable if any body provides me the details regarding altering the attributes of an existing column. Thanks and Regards Sathya
by: Peter Herath | last post by:
I want to create a custormizable report . For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row filed and other one for select column filed in the report) when u select items in that combo boxes and press a Button then selected items should go to the crosstab query as parameters and execute the query. upto that i have done my coding part but...
by: accessbunnie | last post by:
Hello Access Users! I am a bit of an Access novice and am currently creating a database and have come up against a huge (for me!) road block. I do not write in Access code and instead, tend to just use design view and wizards to create my databases. I created a form using a query I created composed of two different tables with the same unique identifier. All things went fine while I dragged my variables from the field list to the...
by: natalie99 | last post by:
Hi All I thought this would be easy but I am having great difficulty! Could someone kindly please explain where I am going wrong? I have a single table of data, tblA each line shows a start/stop, country, date and price eg. lines: Action-------Country----------Date-------------------Value
by: m jansi | last post by:
Hi i have the table employee with columns emp_id, emp_name, salary, dep_id and the another table department with columns dep_id, dep_name, manager. dep_id is the foreign key for employee table. i want the query for retrieving emp_id, salary, dep_id i.e. the employee who got maximum salary in each department using group by
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.