Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>First, let's make some test data:</p> <pre><code>create table client (client_id integer not null primary key auto_increment, name varchar(64)); create table portfolio (portfolio_id integer not null primary key auto_increment, client_id integer references client.id, cash decimal(10,2), stocks decimal(10,2)); insert into client (name) values ('John Doe'), ('Jane Doe'); insert into portfolio (client_id, cash, stocks) values (1, 11.11, 22.22), (1, 10.11, 23.22), (2, 30.30, 40.40), (2, 40.40, 50.50); </code></pre> <p>If you didn't need the portfolio ID, it would be easy:</p> <pre><code>select client_id, name, max(cash + stocks) from client join portfolio using (client_id) group by client_id +-----------+----------+--------------------+ | client_id | name | max(cash + stocks) | +-----------+----------+--------------------+ | 1 | John Doe | 33.33 | | 2 | Jane Doe | 90.90 | +-----------+----------+--------------------+ </code></pre> <p>Since you need the portfolio ID, things get more complicated. Let's do it in steps. First, we'll write a subquery that returns the maximal portfolio value for each client:</p> <pre><code>select client_id, max(cash + stocks) as maxtotal from portfolio group by client_id +-----------+----------+ | client_id | maxtotal | +-----------+----------+ | 1 | 33.33 | | 2 | 90.90 | +-----------+----------+ </code></pre> <p>Then we'll query the portfolio table, but use a join to the previous subquery in order to keep only those portfolios the total value of which is the maximal for the client:</p> <pre><code> select portfolio_id, cash + stocks from portfolio join (select client_id, max(cash + stocks) as maxtotal from portfolio group by client_id) as maxima using (client_id) where cash + stocks = maxtotal +--------------+---------------+ | portfolio_id | cash + stocks | +--------------+---------------+ | 5 | 33.33 | | 6 | 33.33 | | 8 | 90.90 | +--------------+---------------+ </code></pre> <p>Finally, we can join to the client table (as you did) in order to include the name of each client:</p> <pre><code>select client_id, name, portfolio_id, cash + stocks from client join portfolio using (client_id) join (select client_id, max(cash + stocks) as maxtotal from portfolio group by client_id) as maxima using (client_id) where cash + stocks = maxtotal +-----------+----------+--------------+---------------+ | client_id | name | portfolio_id | cash + stocks | +-----------+----------+--------------+---------------+ | 1 | John Doe | 5 | 33.33 | | 1 | John Doe | 6 | 33.33 | | 2 | Jane Doe | 8 | 90.90 | +-----------+----------+--------------+---------------+ </code></pre> <p>Note that this returns two rows for John Doe because he has two portfolios with the exact same total value. To avoid this and pick an arbitrary top portfolio, tag on a GROUP BY clause:</p> <pre><code>select client_id, name, portfolio_id, cash + stocks from client join portfolio using (client_id) join (select client_id, max(cash + stocks) as maxtotal from portfolio group by client_id) as maxima using (client_id) where cash + stocks = maxtotal group by client_id, cash + stocks +-----------+----------+--------------+---------------+ | client_id | name | portfolio_id | cash + stocks | +-----------+----------+--------------+---------------+ | 1 | John Doe | 5 | 33.33 | | 2 | Jane Doe | 8 | 90.90 | +-----------+----------+--------------+---------------+ </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POMySQL GROUP BY two columns
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USVebjorn Ljosa
    1. USVebjorn Ljosa
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMySQL GROUP BY two columns
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThat's an amazing answer, and once I'd put ORDER BY maxtotal DESC on the end it's perfect! ;) Thank you
      singulars
      1. PO
      1. USChrisS
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload