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
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.
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)
ASN 61 231 ASNL 0 101 ASR 37 13
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.
SELECT symb, price FROM ( SELECT symb, price, ROW_NUMBER() OVER (PARTITION BY symb ORDER BY price DESC) AS rn FROM tblIssue ) WHERE rn = 1
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
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