Select first record within a segmentation in SQL Server

Question

I have the following example table:

The consultation asks me to show which students entered first in each course. I can tell which student entered the university first, using the function top(1), but how can I do this for each course?

Answer 1

Use the ROW NUMBER function, it will display a sort according to a "break" in the case, by course code.

    SELECT * 
        FROM 
        (SELECT ROW_NUMBER () OVER (PARTITION BY codigo_curso ORDER BY data_ingresso ) as ROW_NUM
, *
         FROM NOME_TABELA
        ) TB_AUX
    WHERE ROW_NUM = 1 

Answer 2

CTE with ROW_NUMBER()

;WITH CTE AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY codigo_curso ORDER BY data_ingresso) AS row
   FROM ALUNOS
)
SELECT *
FROM CTE 
WHERE row= 1

SubQuery Correlated:

SELECT l.*
FROM
  (SELECT ALUNOS.*,
           ROW_NUMBER() OVER (PARTITION BY codigo_curso
                              ORDER BY data_ingresso) AS linha
   FROM ALUNOS) l
WHERE l.linha = 1;

Sqlfiddle

Update

Today I learned a different way of making a query that will generate the same result.

SELECT * 
FROM ALUNOS
INNER JOIN(SELECT codigo_curso, MIN(data_ingresso) data_ingresso
    FROM ALUNOS
    GROUP BY codigo_curso) A
ON ALUNOS.codigo_curso = A.codigo_curso
AND ALUNOS.data_ingresso = A.data_ingresso

• Take the smallest data_ingresso grouped by codigo_curso
• Make a JOIN with that result.

You meant to SQL:

Return students who first joined a certain course.

• PARTITION BY value_expression - value_expression specifies the column by which the result set is partitioned.
• Order_by_clause - Determines the orders in which lines will be assigned.

Row Number in a nutshell will assign an order to your lines.

Recommended Readings:

• _{What is ROW_NUMBER?}
• _{ROW_NUMBER (Transact-SQL)}

Answer 3

Interesting question. I searched and found that answer:

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY codigo_curso ORDER BY data_ingresso DESC) AS rn
   FROM tusu
)
SELECT *
FROM cte
WHERE rn = 1

I only used it for testing, but it seems to work for what you need.