Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Let's assume I have 3 tables: videos, books, albums and I would like a query that gives me the 3 latest entries in each table, along with a column "media_type" valued Video, Book, or Album, accordingly.

I know how to get the 3 latest entries from each table, but how do I create the temporary column?

share|improve this question
You mean something like SELECT b.*, 'books' as table_name FROM books b? – dezso Dec 3 '12 at 20:24

Perhaps this is what you are looking for:

(SELECT id, name, 'Book' AS media_type
FROM books
LIMIT 0,3) 


(SELECT id, name, 'Video' AS media_type
FROM videos
LIMIT 0,3)


(SELECT id, name, 'Album' AS media_type
FROM albums
LIMIT 0,3);

I don't know if you need any particular ordering or not. According to the docs, UNION produces an unordered set of rows. You should be able to add an ORDER BY after the last SELECT, outside the parentheses.

share|improve this answer
There is no need to put each select of a UNION into parentheses. – a_horse_with_no_name Dec 3 '12 at 23:34
When I tested it, without the parentheses the LIMIT was only applying to the entire set. I was only getting 3 rows returned--the books. I'm running an older version (5.0.96), perhaps that behavior has changed. – mdoyle Dec 4 '12 at 15:50

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.