从具有最大值的组中选择记录 [英] SELECT Record From Group With Max Value

问题描述

大家好


我正在尝试为数据库编写一个特定的查询，记录影响公司的不同问题，但它不能正常工作。

主要字段包括大约10个不同的问题配置项，问题描述本身以及取决于影响/严重程度的分数。


我想要的是显示每个配置项的最高得分，将返回10行，每个配置一个。我需要将问题描述与高分相对应。


我尝试在配置上使用''group by''，在分数上使用'max''和''first' '问题描述返回10行，但问题与分数不符。我已经尝试过它''最后''而且还有其他人不能正常工作。


如果有办法可以做任何想法吗？


谢谢

Gareth

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

推荐答案

hi''

据我所知

你不需要使用第一个或最后一个，直接使用列。


试试这个

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

try this

展开 | 选择 | 换行 | 行号

这个Gareth有两种基本方法。

There are two basic ways of doing this Gareth.

1. 更纯粹的方法是使用subquer y并通过PK链接记录。你没有在你的问题中指出你的PK是什么，也不知道你是否有一张桌子。
2. 如果你知道[Score]字段的可能范围，那么你可以连接得分和描述，排序，然后重新提取描述（我假设在这个例子中得分从不超过100）：
   

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

展开 | 选择 | 换行 | 行号

谢谢两位，我会在星期一试试，并告诉你我是怎么过的。

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

这篇关于从具有最大值的组中选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！