469,282 Members | 1,977 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

SELECT Record From Group With Max Value

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
Aug 13 '11 #1

✓ answered by 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 :
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])

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

try this
Expand|Select|Wrap|Line Numbers
  1. Select [issuedescription] ,max(Score) 
  2. from table_name
  3. group by issuedescription
  4. order by issuedescription
  5.  
  6.  
Aug 13 '11 #2
NeoPa
32,173 Expert Mod 16PB
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) :
    Expand|Select|Wrap|Line Numbers
    1. SELECT   [Config Item]
    2.        , Mid(Max(Format([Score], '000') & [Description]), 4, 999)
    3.        , [Score]
    4. FROM     [YourTable]
    5. 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.
Aug 13 '11 #3
Thanks both, I will try these Monday and will let you know how I get on.
Aug 13 '11 #4
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

Expand|Select|Wrap|Line Numbers
  1. SELECT form2query.[Config ID], Max(form2query.[CS Impact Score]) AS [MaxOfCS Impact Score]
  2. FROM form2query
  3. 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.

Expand|Select|Wrap|Line Numbers
  1. SELECT form2query.[Config ID], Max(form2query.[CS Impact Score]) AS [MaxOfCS Impact Score], form2query.[Issue Description]
  2. FROM form2query
  3. GROUP BY form2query.[Config ID];
  4.  
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
Aug 15 '11 #5
NeoPa
32,173 Expert Mod 16PB
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.
Aug 15 '11 #6
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."

Expand|Select|Wrap|Line Numbers
  1. SELECT   [Config ID] 
  2.        , Mid(Max(Format([CS impact Score], '000') & [Issue Description]), 4, 999) 
  3.        , [CS impact Score] 
  4. FROM     [form2query] 
  5. GROUP BY [Config ID] 
Aug 15 '11 #7
NeoPa
32,173 Expert Mod 16PB
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) :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Config ID]
  2.        , Mid(Max(Format([CS Impact Score], '000') & [Issue Description]), 4, 999) AS [IssueDescription]
  3. FROM     [form2query]
  4. GROUP BY [Config ID]
Aug 15 '11 #8
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?
Aug 15 '11 #9
NeoPa
32,173 Expert Mod 16PB
Yes there is (See post #3). You'll see from that post though, that you'll need a unique identifier for the record.
Aug 15 '11 #10
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
NeoPa
32,173 Expert Mod 16PB
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
NeoPa
32,173 Expert Mod 16PB
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
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
NeoPa
32,173 Expert Mod 16PB
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
Yes the error above was using the code you gave me.
Aug 16 '11 #16
NeoPa
32,173 Expert Mod 16PB
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
Thats it! Works perfectly. Thanks for all your help.
Aug 16 '11 #18
NeoPa
32,173 Expert Mod 16PB
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.
Aug 16 '11 #19

Post your reply

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

Similar topics

5 posts views Thread by Jimmy Tran | last post: by
2 posts views Thread by Don Wash | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.