SELECT Record From Group With Max Value

[Gareth Jones]

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?

Thanks
Gareth

[Dody]

hi '
as i understand
you not need to use first or last, use column directly.

try this

Select [issuedescription] ,max(Score) 
from table_name
group by issuedescription
order by issuedescription
 
 

[NeoPa]

There are two basic ways of doing this Gareth.

1. The purer way is to use a subquery and link the records via the PK. You don't indicate what your PK is in your question, nor even whether you have one for the table.
2. If you know the possible range of the [Score] field then you can concatenate the score and the description, sort on that, then re-extract the description (I'll assume the Score never exceeds 100 in this example) :
   

   SELECT   [Config Item]
          , Mid(Max(Format([Score], '000') & [Description]), 4, 999)
          , [Score]
   FROM     [YourTable]
   GROUP BY [Config Item]

PS. I thought it may help to explain your situation more clearly as your explanation left much unclear.
You have records in an Issue table which cover ten different [Config Item]s. The fields in the table include [Config Item], [Description] and [Score]. There can be zero or many records for each [Config Item]. You would like each [Config Item] with records to be displayed, including the highest [Score] and the [Description] from the record where the highest [Score] was found.

[Gareth Jones]

Thanks both, I will try these Monday and will let you know how I get on.

[Gareth Jones]

Hi all,

I tried the above however I get the below error

"You tried to execute a query that does not include the specified expression 'CS Impact Score' as part of an aggregate function."

When I trim the code down and use the below as I tried originally, it works

SELECT form2query.[Config ID], Max(form2query.[CS Impact Score]) AS [MaxOfCS Impact Score]
FROM form2query
GROUP BY form2query.[Config ID];

However when I introduce further fields such as the 'issue description', it fails and gets the above error message again.

SELECT form2query.[Config ID], Max(form2query.[CS Impact Score]) AS [MaxOfCS Impact Score], form2query.[Issue Description]
FROM form2query
GROUP BY form2query.[Config ID];
 

When looking at the code in design view, it changes the TOTAL field for the issue description to 'Expression'

I have tried all the different fields in the TOTAL drop down and they either fail or it returns too many fields.

Thanks
Gareth

[NeoPa]

Gareth, you're going off on a tangent. Let's deal with the issue of the thread first - the question you asked. When that is resolved satisfactorily, it may make sense to extend the question along similar lines (acceptable sometimes) or post a new question. The first step though, is to progress with the original question. I see no evidence of your code following either of my suggestions, so the next step is yours still.

[Gareth Jones]

When I try yours, I get the below error, I have included your code below the error.

"You tried to execute a query that does not include the specified expression 'CS Impact Score' as part of an aggregate function."

SELECT   [Config ID] 
       , Mid(Max(Format([CS impact Score], '000') & [Issue Description]), 4, 999) 
       , [CS impact Score] 
FROM     [form2query] 
GROUP BY [Config ID] 

[NeoPa]

That's because I was being a numpty Gareth :-D I can't believe I posted that as was. Here's a version that has a chance of working (Apologies for the delay getting it right) :

SELECT   [Config ID]
       , Mid(Max(Format([CS Impact Score], '000') & [Issue Description]), 4, 999) AS [IssueDescription]
FROM     [form2query]
GROUP BY [Config ID]

[Gareth Jones]

No probs :)

Its still not working however the error has changed to the below:

"Cannot have Memo, OLE or Hyperlink Object fields in an aggregate arguement ([Issue Description])"

The field [Issue Description] is a memo field as opposed to text as it contains quite a bit of text. Is there anyway around this?

[NeoPa]

Yes there is (See post #3). You'll see from that post though, that you'll need a unique identifier for the record.

[Gareth Jones]

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.

[NeoPa]

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.) :

SELECT   qryF2.[Config ID]
       , subQ.[CS Impact Score])
       , subQ.[Issue Description]
FROM     [form2query] AS qryF2
         INNER JOIN
         (SELECT [Config ID]
               , [CS Impact Score]
               , [Issue Description]
         ) AS subQ
  ON     qryF2.[Config ID] = subQ.[Config ID]
HAVING   (Max(qryF2.[CS Impact Score]) = subQ.[CS Impact Score])
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.

[NeoPa]

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.

[Gareth Jones]

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

[NeoPa]

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.

[Gareth Jones]

Yes the error above was using the code you gave me.

[NeoPa]

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 :

SELECT qF2.*
FROM   [Form2Query] AS qF2
       INNER JOIN
       (SELECT   [Config ID]
               , Max([CS Impact Score]) AS [MaxCSImpactScore]
        FROM     [Form2Query]
        GROUP BY [Config ID]
       ) AS sQ
  ON   (qF2.[Config ID] = sQ.[Config ID])
 AND   (qF2.[CS Impact Score] = sQ.[MaxCSImpactScore])

[Gareth Jones]

Thats it! Works perfectly. Thanks for all your help.

[NeoPa]

No worries Gareth. Pleased to hear that worked for you.

BTW, I noticed a small overcomplication 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.