Create and fetch rows from a static enumerated list in MySQL

• 
  

  SELECT lists.label, lists.value FROM ((SELECT "Item 1" AS label, 1 AS value) UNION (SELECT "Item 2" AS label, 2 AS value) UNION (SELECT "Item 3" AS label, 3 AS value)) AS lists ORDER BY lists.label ASC

• Result/Output
  
  +--------+-------+ | label | value | +--------+-------+ | Item 1 | 1 | | Item 2 | 2 | | Item 3 | 3 | +--------+-------+
• Explanation
  Create and fetch rows from a static enumerated list in MySQL. Once you have the list, it's easy to join against and/or use sub-queries to retrieve/aggregate additional information. This little trick is super handy for writing SQL reports, where storing your list data in a lookup/lists table just doesn't make sense.
  
  

  +--------+-------+
  | label  | value |
  +--------+-------+
  | Item 1 |     1 | 
  | Item 2 |     2 | 
  | Item 3 |     3 | 
  +--------+-------+
  

Posted 01/18/2011 07:07PM by ngeren 000

• Link
• Embed <!-- BEGIN Sugr SNIPPET --> <script src="http://www.syntacticsugr.com/embed_html?sugr=106" type="text/javascript"></script> <script type="text/javascript">SyntaxHighlighter.config.stripBrs = true; SyntaxHighlighter.all();</script> <!-- END Sugr SNIPPET -->