SQL Query to select bottom 2 from each category

Question

In Mysql I want to select the bottom 2 items from each category

Category Value 1 1.3 1 4.8 1 3.7 1 1.6 2 9.5 2 9.9 2 9.2 2 10.3 3 4 3 8 3 16 

Giving me:

 Category Value 1 1.3 1 1.6 2 9.5 2 9.2 3 4 3 8 

Before I switched from sqlite3, I had to first select the lowest of each category, and then exclude everything that was connected with it, I had to choose the lowest of each category again. Then everything that matches this new lowest or smaller category wins. This would also select more than 2 in the case of a tie, which was unpleasant ... He also had a lot of time.

My ultimate goal is to count the number of times a person is in one of the least 2 categories (there is also a name field), and this is the part that I don’t know how to do. Thanks

Answer 1

You can try the following:

 SELECT * FROM ( SELECT c.*, (SELECT COUNT(*) FROM user_category c2 WHERE c2.category = c.category AND c2.value < c.value) cnt FROM user_category c ) uc WHERE cnt < 2 

It should give you the desired results, but check if the performance is satisfactory.

Answer 2

 SELECT c1.category, c1.value FROM catvals c1 LEFT OUTER JOIN catvals c2 ON (c1.category = c2.category AND c1.value > c2.value) GROUP BY c1.category, c1.value HAVING COUNT(*) < 2; 

Tested on MySQL 5.1.41 with your test data. Exit:

 +----------+-------+ | category | value | +----------+-------+ | 1 | 1.30 | | 1 | 1.60 | | 2 | 9.20 | | 2 | 9.50 | | 3 | 4.00 | | 3 | 8.00 | +----------+-------+ 

(Additional decimal places are that I declared the value column as NUMERIC(9,2) .)

Like other solutions, this gives more than 2 lines for each category, if there are links. There are ways to create a join condition to solve it, but we will need to use a primary key or a unique key in your table, and we will also need to know how you intend to bind the relationships.

Answer 3

The union must work. I'm not sure about performance compared to Peter’s solution.

 SELECT smallest.category, MIN(smallest.value) FROM categories smallest GROUP BY smallest.category UNION SELECT second_smallest.category, MIN(second_smallest.value) FROM categories second_smallest WHERE second_smallest.value > (SELECT MIN(smallest.value) FROM categories smallest WHERE second.category = second_smallest.category) GROUP BY second_smallest.category 

Answer 4

Here is a very generalized solution that will work to select the first n lines for each category. This will work even if there are duplicates in value.

 /* creating temporary variables */ mysql> set @cnt = 0; mysql> set @trk = 0; /* query */ mysql> select Category, Value from (select *, @cnt:=if(@trk = Category, @cnt+1, 0) cnt, @trk:=Category from user_categories order by Category, Value ) c1 where c1.cnt < 2; 

Here is the result.

 +----------+-------+ | Category | Value | +----------+-------+ | 1 | 1.3 | | 1 | 1.6 | | 2 | 9.2 | | 2 | 9.5 | | 3 | 4 | | 3 | 8 | +----------+-------+ 

This is tested on MySQL 5.0.88. Note that the initial value of the @trk variable should not be the smallest value of the Category field.

Answer 5

Here is a solution that handles duplicates correctly. The table name is "zzz" and the columns are int and float

 select smallest.category category, min(smallest.value) value from zzz smallest group by smallest.category union select second_smallest.category category, min(second_smallest.value) value from zzz second_smallest where concat(second_smallest.category,'x',second_smallest.value) not in ( -- recreate the results from the first half of the union select concat(c.category,'x',min(c.value)) from zzz c group by c.category ) group by second_smallest.category order by category 

Cautions:

• If there is only one value for this category, only one record is returned.
• If each row had a unique record identifier, you do not need all concats to simulate a unique key.

Your mileage may vary.

- Mark