title: Subquery select using UNION
body: <pre><code>CREATE TABLE newsarticles
      (katernenID int, Buitenland int, Economie int, Sport int, Cultuur int, Wetenschap int, Media int, userID int);
  
  INSERT INTO newsarticles
      (katernenID, Buitenland, Economie, Sport, Cultuur, Wetenschap, Media, userID)
  VALUES
      (1, 24, 5, 52, 3, 2, 3, 1),
      (3, 2, 0, 0, 1, 51, 66, 5),
      (4, 50, 2, 67, 41, 1, 54, 6),
      (5, 30, 1, 79, 0, 0, 12, 7),
      (6, 9, 26, 0, 100, 11, 0, 8),
      (7, 2, 14, 8, 1, 33, 98, 9),
      (8, 0, 1, 0, 1, 0, 0, 10);
  
  
  
  SELECT
    userID,GROUP_CONCAT(amt ORDER BY amt DESC) AS four_highest
  FROM
  (
    SELECT userID, @rownum := @rownum + 1 AS rank, NAME, amt FROM (
      SELECT userID, Buitenland AS amt, 'Buitenland' AS NAME UNION
      SELECT userID, Economie, 'Economie'  UNION
      SELECT userID, Sport, 'Sport' UNION
      SELECT userID, Cultuur, 'Cultuur'  UNION
      SELECT userID, Wetenschap, 'Wetenschap'  UNION
      SELECT userID, Media, 'Media' 
           FROM( SELECT katernenID, Buitenland, Economie, Sport, Cultuur, Wetenschap, Media, userID FROM newsarticles  ) temp
  
    ) amounts, (SELECT @rownum := 0) r
  ) top4
  GROUP BY userid
  </code></pre>
  
  <p>I want to make UNION like in above query, But Union is not Working. UNION needs FROM statement immediately.
  Like:</p>
  
  <pre><code>    SELECT userID, Wetenschap, 'Wetenschap'  
                   FROM( SELECT katernenID, Buitenland, Economie, Sport, Cultuur, Wetenschap, Media, userID FROM newsarticles  ) temp
                   UNION
      SELECT userID, Media, 'Media' 
           FROM( SELECT katernenID, Buitenland, Economie, Sport, Cultuur, Wetenschap, Media, userID FROM newsarticles  ) temp
  </code></pre>
  
  <p>But I can't repeat FROM statement, there are too many calculations in subquery. Is there a way to do this or am I missing something?</p>
  
  <p>OR</p>
  
  <p>i want a new column with max(a_percentage,b_percentage,c_percentage),SecondMAX(a_percentage,b_percentage,c_percentage),ThirdMAX(a_percentage,b_percentage,c_percentage)</p>
  
  <pre><code>    SELECT id,a,b,c,a_percentage,b_percentage,c_percentage
      -- -------------------------max percentage
      GREATEST(axis_per, hdfc_per,icici_per,citi_per ) AS max_per,
      FROM (
        SELECT id,a,b,c,
        IFNULL(a*100/a+b+c+d,0) AS a_percentage,
        IFNULL(b*100/a+b+c+d,0) AS b_percentage,
        IFNULL(c*100/a+b+c+d,0) AS c_percentage
        FROM test
          WHERE TIME &gt; '2012-01-01'
          GROUP BY id
        ) A
  </code></pre>