Today's Question: 

Hide

Different programming are similar in helping people build staff work as people want them to. But they all have their own features which differentiate them from other programming languages. The language type may be different, for example Java is OOP, some may have different syntax. A programming motto usually can best describe the characteristic of the programming language. For example, "Write once Run everywhere" for Java.Do you know any other programming language motto?
Do you know the mottos of programmig languages?
Description My Answer

Technical Article => Database =>  MySQL

Select top 3 values from each group in a table with SQL

Source : Pi Ke    Date : 2013-05-23 03:21:25  

Yesterday, my friend Liu Bing asked me a question about how to select top 3 values for each person in a database table using SQL. I think this question is interesting and it deserves some thoughts. Here I record down how to solve this issue.

Assume we have a table which has two columns, one column contains the names of some people and the other column contains some values related to each person. One person can have more than one value. Each value has a numeric type. The question is we want to select the top 3 values for each person from the table. If one person has less than 3 values, we select all the values for that person. Also assume there is no duplicate entry in the table, i.e each name/value pair will be different.

How do we implement the above in SQL? Let's take a look the table structure:

CREATE TABLE tbl (
	name VARCHAR(20) NOT NULL,
	value int(11) DEFAULT NULL
);

INSERT INTO tbl VALUES ('Pei Kai',5);
INSERT INTO tbl VALUES ('Pei Kai',3);
INSERT INTO tbl VALUES ('Pi Ke Kai',2);
INSERT INTO tbl VALUES ('Pei Kai',10);
INSERT INTO tbl VALUES ('Pei Kai',6);
INSERT INTO tbl VALUES ('Pi Ke',4);
INSERT INTO tbl VALUES ('Pei Kai',20);
INSERT INTO tbl VALUES ('Pei Kai',1);
INSERT INTO tbl VALUES ('Pi Ke',25);
INSERT INTO tbl VALUES ('Pi Ke',3);
INSERT INTO tbl VALUES ('Liu Bing',4);
INSERT INTO tbl VALUES ('Liu Bing',7);

The data will be like :

mysql> select * from tbl;
+-----------+-------+
| name      | value |
+-----------+-------+
| Pei Kai   |     5 |
| Pei Kai   |     3 |
| Pi Ke Kai |     2 |
| Pei Kai   |    10 |
| Pei Kai   |     6 |
| Pi Ke     |     4 |
| Pei Kai   |    20 |
| Pei Kai   |     1 |
| Pi Ke     |    25 |
| Pi Ke     |     3 |
| Liu Bing  |     4 |
| Liu Bing  |     7 |
+-----------+-------+

Actually the most difficult part here is to get top 3 values for each person instead of getting top 3 values for all people. We need to find out the 3rd largest value for each person and then select the values which is larger than or equal to that value for each person. Since we need to find the values for each person, so we have to have a loop here to loop through each person. In pure SQL, we can rely on correlated query to simulate the for loop.

Below is the complete query which is to get the top 3 values for each person.

SELECT t.name,t.value
FROM (
	(
		SELECT t1.name,t1.value FROM tbl t1 
		INNER JOIN
		(
			#Below is a correlated query where the outer query simulates a for loop
			SELECT t2.name,t2.value 
			FROM tbl t2
			WHERE 2=(  # Here 2 can be replaced by (N-1) to select top N values for each name
				SELECT COUNT(DISTINCT(t3.value))
				FROM tbl t3 
				WHERE t3.value>t2.value AND t2.name=t3.name
			)
		) t4
		ON t1.name=t4.name 
		WHERE t1.value>=t4.value
	)
	UNION ALL
	#Below is to select all name/value pairs where each name has less 
	#than 3 values
	(
	SELECT t5.name,t5.value FROM tbl t5 
	WHERE t5.name IN (SELECT t6.name 
	FROM tbl t6
	GROUP BY t6.name HAVING COUNT(1)<3)
	)
) t
ORDER BY t.name ASC,t.value DESC
;

The test result will be :

+-----------+-------+
| name      | value |
+-----------+-------+
| Liu Bing  |     7 |
| Liu Bing  |     4 |
| Pei Kai   |    20 |
| Pei Kai   |    10 |
| Pei Kai   |     6 |
| Pi Ke     |    25 |
| Pi Ke     |     4 |
| Pi Ke     |     3 |
| Pi Ke Kai |     2 |
+-----------+-------+

The above query can work perfectly if there is no duplicate entry in the table. However, if there are duplicates, there may be issues. It may return some extra values for some people. Since the correlated query above cannot handle the duplicates very well.

Now lets insert two more values:

INSERT INTO tbl VALUES ('Pi Ke',7);
INSERT INTO tbl VALUES ('Pi Ke',7);

Then let's run the above query again, the result now becomes:

+-----------+-------+
| name      | value |
+-----------+-------+
| Liu Bing  |     7 |
| Liu Bing  |     4 |
| Pei Kai   |    20 |
| Pei Kai   |    10 |
| Pei Kai   |     6 |
| Pi Ke     |    25 |
| Pi Ke     |     7 |
| Pi Ke     |     7 |
| Pi Ke     |     4 |
| Pi Ke Kai |     2 |
+-----------+-------+

Here for Pi Ke, there are 4 records returned.

If you have good solution for the above duplicate issue. Please let us know.

Save as PDF Mark as read Mark as important
By clicking the "Mark as read" button, this article will be marked as read. It will be removed from the homepage's latest news and the article list on the "Technical article" page in following visits and it will be put to your read list which you can find in "Amin->Article read list". There you can unmark the read articles.
By clicking the "Mark as important" button, this article will be put to your important article list which you can find in "Amin->Article important list". Later when you want reread this article, it's easier for you to find it by checking the "Article important list".

Tags : SQL,Correlated query,top 3   Read(1495) Comment(0) Version(0)

Share on Facebook  Share on Twitter  Share on Google+  Share on Weibo  Share on Reddit  Share on Digg  Share on Tumblr    Delicious 

 Previous : A tour to Tumblr's office
 Next : Content based HTTP Cache

  ::Related Articles

  ::Comment Zone  (Total 0 comment)

No comment for this article.

  ::Comment

Nickname  
Email 
Comment

:: Other versions

No other versions available yet.

:: Recent articles

:: Most read

:: Contribute

Want to share with the world your understanding about technology? Want to record the process you solve a technical problem? Want to make the world benefit from your understanding and solution? Write them down. You make the world better, the world makes us better.

Write article

:: Find us

Back to top