title: How can I select an ID from the row with the maximum value for each of several columns?
body: <p>I want to extract the values of an ID column from the rows containing the maximum values of each of several other columns, and then collate these in a new table which has the column headers in one column and the row IDs of the maximum values in another.</p>
  
  <p>To clarify:</p>
  
  <p>My data is structured like this, extending into a roughly 20x20 grid:</p>
  
  <pre><code>table:
  
   id | name  | A | B | C | D | E | ... 
  ----|-------|---|---|---|---|---|----
  101 | name1 | 4 | 4 | 1 | 3 | 3 | ... 
  102 | name2 | 3 | 1 | 2 | 4 | 2 | ... 
  103 | name3 | 2 | 2 | 3 | 2 | 1 | ... 
  104 | name4 | 1 | 3 | 4 | 1 | 4 | ... 
  ... | ..... | . | . | . | . | . | ... 
  </code></pre>
  
  <p>And I want a query that returns the <code>name</code> which contains the highest value for each column <code>A</code>, <code>B</code>, <code>C</code> etc., like so:</p>
  
  <pre><code>top_scorers:
  
  id | top_scorer
  ---|-----------
   A | name1
   B | name1
   C | name4
   D | name2
   E | name4
  </code></pre>
  
  <p>In this case I know the maximum values will all be the same so I can search for values <code>= 4</code>, but it might not be in future so finding the max would be a more robust method.</p>
  
  <p>The best I've come up with so far is:</p>
  
  <pre><code>SELECT name AS top_scorer FROM table
  WHERE A = 4
  UNION
  SELECT name AS top_scorer FROM table
  WHERE B = 4
  UNION
  ...
  </code></pre>
  
  <p>And so on, repeating for each column header.</p>
  
  <p>I hoped this would generate the <code>top_scorer</code> column above, which I could then join to another table containing the column headers of my original table. However it does not return any <code>name</code> more than once, so I only get as many rows are there are distinct entries in the top_scorer column. The resulting values are also sorted alphabetically rather than in the order they're first found.</p>
  
  <p>So rather than</p>
  
  <pre><code>top_scorer
  ----------
  name1
  name1
  name4
  name2
  name4
  </code></pre>
  
  <p>the code above returns</p>
  
  <pre><code>top_scorer
  ----------
  name1
  name2
  name4
  </code></pre>
  
  <p>I also tried:</p>
  
  <pre><code>SELECT name AS top_scorer FROM table
  WHERE A = MAX(A)
  UNION
  SELECT name AS top_scorer FROM table
  WHERE B = MAX(B)
  UNION
  ...
  </code></pre>
  
  <p>But this gave an error: "misuse of aggregate function MAX()"</p>
  
  <p>Can anyone suggest a query that would return the <code>top_scorers</code> table above?</p>
  
  <p>Similar questions I've found are either looking to return maximums from one column using <code>GROUP BY</code>, or the maximum value across a range of columns for each row  (equivalent to the highest score achieved by each <code>name</code> in this case), which aren't what I'm looking for.</p>
  
  <p>Is there maybe something clever that can be done with <code>PIVOT</code> or some other transposing function? I'm a SQL beginner and haven't tried using anything like that before.</p>
  
  <p>This is running on DB Browser for SQLite.</p>