MySQL – getting two items per source in a single query
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?
I'm a Web Developer living in Cape Town, South Africa. Interests include: PHP, JQuery, ExtJS, Adobe AIR, Linux, Ubuntu, WordPress, Human Rights, Usability, Multimedia
jonmcrawford
You’ll want to do a correlated subquery like so:
DROP TABLE #stories
CREATE TABLE #stories (id int, source varchar(7),title varchar(8),storydate datetime)
INSERT INTO #stories VALUES (10,'cnn','Story 10','1/21/2010')
INSERT INTO #stories VALUES (9,'bbcnews','Story 9','1/20/2010')
INSERT INTO #stories VALUES (8,'bbcnews','Story 8','1/19/2010')
INSERT INTO #stories VALUES (7,'skynews','Story 7','1/18/2010')
INSERT INTO #stories VALUES (6,'cnn','Story 6','1/17/2010')
INSERT INTO #stories VALUES (5,'bbcnews','Story 5','1/16/2010')
INSERT INTO #stories VALUES (4,'cnn','Story 4','1/15/2010')
INSERT INTO #stories VALUES (3,'skynews','Story 3','1/14/2010')
INSERT INTO #stories VALUES (2,'skynews','Story 2','1/13/2010')
INSERT INTO #stories VALUES (1,'cnn','Story 1','1/12/2010')
SELECT DISTINCT
s.source,
s.title,
s.storydate
FROM #stories s
ORDER BY source, storydate desc
SELECT DISTINCT
s.source,
s.title,
s.storydate
FROM #stories s
WHERE s.storydate IN (SELECT TOP 2 s2.storydate FROM #stories s2 WHERE s2.source=s.source)
ORDER BY source, storydate desc
reference found here http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html
Note that we’re using WHERE IN (subquery) because we want to return more than one date on the inner resultset.
Hope that helps!
Jon Crawford