按最大分组 [英] Group by Max

问题描述

  SELECT tblIssue.SYMB，tblIssue。[PRCE]，tblIssue。[股东] 
 FROM tblIssue 
  
 
 SYMB价格股东
 ASN $ 0.00 0 
 ASN $ 0.00 51 
 ASN $ 25.18 0 
 ASN $ 25.26 0 
 ASN $ 36.00 0 
 ASN $ 60.62 231 
 ASNL $ 0.00 101 
 ASR $ 0.00 4 
 ASR $ 0.00 24 
 ASR $ 37.17 13 
 
 
 
解决方案
 
  SELECT i1。* 
来自tblIssue i1 
左外连接tblIssue i2 
 ON（i1。[SYMB] = i2。[SYMB] AND i1。[股东]< i2 。[股东]）
 i2。[SYMB]为NULL； 
  
这是我用来解决此类问题的技巧：向我显示与<$ c相对应的行$ c> i1 ，当没有其他行 i2 具有相同的 [SYMB] 以及更大的 [股东] 。即如果不存在具有更大的 [Shareholder] 的行，则 i1 必须具有最大的值。
 
 
 
当每个的唯一值具有最高 [Shareholder] 值时，此查询返回多行[ SYMB] ，但是其他人在此线程上给出的其他大多数答案也是如此。为了解决这个问题，您必须使用表的唯一列将其他条件添加到联接中。
 
SELECT tblIssue.SYMB, tblIssue.[PRCE], tblIssue.[Shareholder]
FROM tblIssue
I am trying to pull the symb and price for the maximum number of shareholder per symb. For example, I would have only 1 line for ASN where the price would be $60.62.
SYMB    Price   Shareholder
ASN $0.00   0
ASN $0.00   51
ASN $25.18  0
ASN $25.26  0
ASN $36.00  0
ASN $60.62  231
ASNL    $0.00   101
ASR $0.00   4
ASR $0.00   24
ASR $37.17  13

解决方案
SELECT i1.*
FROM tblIssue i1
  LEFT OUTER JOIN tblIssue i2
  ON (i1.[SYMB] = i2.[SYMB] AND i1.[Shareholder] < i2.[Shareholder])
WHERE i2.[SYMB] IS NULL;
This is a trick I use with these sorts of problems:  show me the row corresponding to i1 when there is no other row i2 with the same [SYMB] and a greater [Shareholder].  I.e. if no row with a greater [Shareholder] exists, then i1 must have the greatest value.

This query returns multiple rows when there's a tie for the highest [Shareholder] value per distinct value of [SYMB], but the same is true for most of the other answers given by other people on this thread.  To solve this, you have to add another condition to the join using a unique column of the table.

                        
这篇关于按最大分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！