I am creating an app with rails.
I'm stuck with how to write SQL statements, so please teach me.
want to
From this, narrow down by "competition_id" and extract only one item with the smallest "id".
For example,
+----------------+----+---------------------------+----------------+-------+-----+
| competition_id | id | image | name | count | rnk |
+----------------+----+---------------------------+----------------+-------+-----+
| 1 | 4 | monster4.jpeg | monster4 | 7 | 1 |
| 2 | 6 | monster2.jpeg | monster2 | 1 | 1 |
| 3 | 9 | monster1.jpeg | monster1 | 1 | 1 |
| 5 | 22 | drink_sample.jpeg | drink6 | 2 | 1 |
| 6 | 33 | sumo_wrestler_sample.jpeg | sumo_wrestler7 | 2 | 1 |
| 7 | 40 | movie_sample.jpeg | movie4 | 2 | 1 |
| 8 | 50 | food_sample.jpeg | food4 | 2 | 1 |
| 9 | 61 | color_sample.jpeg | color5 | 3 | 1 |
| 10 | 72 | book_sample.jpeg | book6 | 2 | 1 |
| 11 | 82 | book_sample.jpeg | book6 | 3 | 1 |
+----------------+----+---------------------------+----------------+-------+-----+
Current status
+----------------+----+---------------------------+----------------+-------+-----+
| competition_id | id | image | name | count | rnk |
+----------------+----+---------------------------+----------------+-------+-----+
| 1 | 4 | monster4.jpeg | monster4 | 7 | 1 |
| 2 | 6 | monster2.jpeg | monster2 | 1 | 1 |
| 3 | 9 | monster1.jpeg | monster1 | 1 | 1 |
| 5 | 22 | drink_sample.jpeg | drink6 | 2 | 1 |
| 6 | 33 | sumo_wrestler_sample.jpeg | sumo_wrestler7 | 2 | 1 |
| 6 | 34 | sumo_wrestler_sample.jpeg | sumo_wrestler8 | 2 | 1 |
| 6 | 35 | sumo_wrestler_sample.jpeg | sumo_wrestler9 | 2 | 1 |
| 7 | 40 | movie_sample.jpeg | movie4 | 2 | 1 |
| 7 | 43 | movie_sample.jpeg | movie7 | 2 | 1 |
| 7 | 45 | movie_sample.jpeg | movie9 | 2 | 1 |
| 8 | 50 | food_sample.jpeg | food4 | 2 | 1 |
| 8 | 56 | food_sample.jpeg | food10 | 2 | 1 |
| 9 | 61 | color_sample.jpeg | color5 | 3 | 1 |
| 10 | 72 | book_sample.jpeg | book6 | 2 | 1 |
| 11 | 82 | book_sample.jpeg | book6 | 3 | 1 |
+----------------+----+---------------------------+----------------+-------+-----+
The code that extracted this
SELECT * FROM (SELECT *,RANK() OVER (PARTITION BY competition_id ORDER BY COUNT DESC) rnk FROM (SELECT items.competition_id,items.id,items.image,items.name,count(*) AS count FROM chosenitems INNER JOIN items ON chosenitems.item_id = items.id GROUP BY items.competition_id,items.id) AS t) AS tt WHERE rnk = 1;
Table structure
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…