Group by Max

问题:

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

回答1:

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.

回答2:

A correlated subquery seems the easiest (if I understand your problem):

select symb, price, shareholder
from issue i
where price = ( select max(price) from issue where symb = i.symb)

which produces:

ASN 61 231
ASNL 0 101
ASR 37 13

回答3:

WITH mx AS (
    SELECT tblIssue.SYMB, MAX(tblIssue.[Shareholder])
    FROM tblIssue
)
SELECT tblIssue.SYMB, tblIssue.[PRCE], tblIssue.[Shareholder]
FROM tblIssue
INNER JOIN mx
    ON mx.SYMB = tblIssue.SYMB
    AND mx.[Shareholder] = tblIssue.[Shareholder]

This should produce:

SYMB    Price   Shareholder
ASN $60.62  231
ASNL    $0.00   101
ASR $0.00   24

If that's what you are looking for.

回答4:

In Oracle:

SELECT symb, price
FROM (
  SELECT symb, price, ROW_NUMBER() OVER (PARTITION BY symb ORDER BY price DESC) AS rn
  FROM   tblIssue
)
WHERE rn = 1

回答5:

That would help;

Select * From tblIssue t2
Join 
(
Select MAX(t1.Shareholder) shrhldr, t1.symb symb
From tblIssue t1
Group by t1.symb
) tt On tt.shrhldr = t2.Shareholder and tt.symb = t2.symb

回答6:

This is similar to Quassnoi's answer but corrected to get the row with max Shareholder which is what the OP asked. If you have multiple rows for the same symbol with the same number of maximum shareholders, it would give you one of them at random. If you want them all, change ROW_NUMBER to RANK.

SELECT symb, price, shareholder
FROM (
  SELECT symb, price, shareholder, ROW_NUMBER() OVER (PARTITION BY symb ORDER BY shareholder DESC) AS rn
  FROM   tblIssue
)
WHERE rn = 1