How to select only the first row of row_number

Asked

Viewed 104 times

0

SELECT SA.RA, PP.NOME, SC.COMPLEMENTO, SPL.ANO, 
    ROW_NUMBER() OVER(PARTITION BY SA.RA ORDER BY SPL.ANO ASC) 
    AS U_PERLET

I am doing this select, is returning all data with a count, the count is done by each RA and ordered by year. What I needed is to show only the last year recorded, because there are several. I’m putting the ROW_NUMBER and putting by where, the last year will always be the largest number of this count described above. Any hint?

sql-server

  • Use SELECT TOP 1 solves your problem? Your question and the expected result is not very clear.

  • @gmsantos edited the question, it’s clearer??

  • See: https://answall.com/questions/233166/selecionar-primeiro-registro-dentro-de-uma-segmenta%C3%A7%C3%A3o-no-sql-server/233178#233178

  • @anonimo create a subquery then? worked out! thanks

1 answer

0


SELECT
MAX(X.U_PERLET) AS ANO,
X.RA,
X.NOME,
[...]

FROM
(
    SELECT 
    SA.RA, 
    PP.NOME, 
    SC.COMPLEMENTO, 
    SPL.ANO, 
    ROW_NUMBER() OVER(PARTITION BY SA.RA ORDER BY SPL.ANO ASC) AS U_PERLET

    FROM [...]

) AS X
GROUP BY
    X.RA,
    X.NOME,
    [...]

OR

SELECT
SA.RA, 
PP.NOME, 
SC.COMPLEMENTO, 
SPL.ANO, 
MAX(SPL.ANO) AS U_PERLET

FROM [...]

GROUP BY 
    SA.RA, 
    PP.NOME, 
    SC.COMPLEMENTO, 
    SPL.ANO

This answer may also help you:

(Query SQL) Limit the number of rows of a select with Inner Join and Where in

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.