duyanhphamkiller duyanhphamkiller - 2 months ago 7
SQL Question

Get the 2 options with min value for each each student_id

I have table name

m_option
:


m_option_id m_student_id value
1 1 5
2 1 5
3 1 6
4 1 7
5 2 1
6 2 2
7 2 3
8 2 3
9 2 4


I want to get the 2 rows with min
value
for each
m_student_id
:


m_option_id m_student_id value
1 1 5
2 1 5
5 2 1
6 2 2

Answer

You can use the row_number window function for that:

SELECT m_option_id, m_student_id, value
FROM (
    SELECT
        m_option_id, m_student_id, value,
        row_number() OVER (PARTITION BY m_student_id ORDER BY value)
    FROM m_option
) t
WHERE
    row_number <= 2;

row_number will calculate the number of each row within its group. We then use that number to filter the the top 2 rows (i.e. lowest value) from each group.

Alternatively, you could use a LATERAL subquery:

SELECT m_option_id, m_student_id, value
FROM (SELECT DISTINCT m_student_id FROM m_option) s,
     LATERAL (
         SELECT m_option_id, value
         FROM m_option
         WHERE s.m_student_id=m_student_id
         ORDER BY value
         LIMIT 2
     ) t;

This will go through all distinct values of m_student_id and for each one of them will find the top 2 rows using a LATERAL subquery.

Comments