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

Answers


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)

which produces:

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.


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

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

Need Your Help

Javascript - How to escape double and single quotes on Kendo template

c# javascript asp.net-mvc twitter-bootstrap kendo-ui

I'm using kendolistview on my view page(razor). Inside of the kendo template I have this code. It just opens the bootstrap pop-up with. Problem is the "content" data has some " and ' values. So the...

How to set up Glassfish for production?

java glassfish

This is probably a newbie question, but I can't find good answers using Google. So, here I am.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.