Tohir Solomons

MySQL – getting two items per source in a single query

Posted on the February 4th, 2010 under Software and Technology by Tohir

Assume you have the following table called stories with the following data. And you have run the following query:

SELECT * FROM stories ORDER BY storydate DESC

id	source	title	storydate
10	cnn	Story 10	2010-01-21
9	bbcnews	Story 9	2010-01-20
8	bbcnews	Story 8	2010-01-19
7	skynews	Story 7	2010-01-18
6	cnn	Story 6	2010-01-17
5	bbcnews	Story 5	2010-01-16
4	cnn	Story 4	2010-01-15
3	skynews	Story 3	2010-01-14
2	skynews	Story 2	2010-01-13
1	cnn	Story 1	2010-01-12

Now for the challenge. What happens if you are required to get the top two stories from each source? One option is to first get the order of the sources, and then loop through each one of them:

SELECT DISTINCT source FROM stories ORDER BY storydate DESC

<loop “source” as “sourceid”>

      SELECT * FROM stories WHERE source = "{sourceid}" ORDER BY storydate DESC LIMIT 2

</loop>

Another option is to do the in MySQL itself! Unsure if there is a better way of doing this, but here’s mine using UNION and Sub Selects:

(SELECT * FROM stories WHERE source =
(SELECT DISTINCT source FROM stories ORDER BY storydate DESC LIMIT 0,1) ORDER BY storydate DESC LIMIT 2)
UNION
(SELECT * FROM stories WHERE source =
(SELECT DISTINCT source FROM stories ORDER BY storydate DESC LIMIT 1,1) ORDER BY storydate DESC LIMIT 2)
UNION
(SELECT * FROM stories WHERE source =
(SELECT DISTINCT source FROM stories ORDER BY storydate DESC LIMIT 2,1) ORDER BY storydate DESC LIMIT 2)

Explanation:

The bold section are subselects and form the WHERE clause of the query. Here  we want the first, second and third as per source, but only one at a time. Once we have two results per item, we use UNION to join them together.

One thing to note though. Even though it returns the latest two stories per source, the final table is not sorted by storydate! This can be done using PHP sorting techniques. Is there a way of doing this one time in MySQL?