get first record in group by result base on condit

问题:

this is my database structure create database testGroupfirst; go

use testGroupfirst;
go

create table testTbl (

id int primary key identity,name nvarchar(50) ,year int ,degree int , place  nvarchar(50)

)

insert into testTbl values ('jack',2015,50,'giza')
insert into testTbl values ('jack',2016,500,'cai')
insert into testTbl values ('jack',2017,660,'alex')
insert into testTbl values ('jack',2018,666,'giza')
insert into testTbl values ('jack',2011,50,'alex')
insert into testTbl values ('rami',2015,5054,'giza')
insert into testTbl values ('rami',2016,1500,'cai')
insert into testTbl values ('rami',2017,66220,'giza')
insert into testTbl values ('rami',2018,6656,'alex')
insert into testTbl values ('rami',2011,540,'cai')
insert into testTbl values ('jack',2010,50,'cai')
select * from testTbl

this is the result till now

i created simple group by

select name , year ,degree ,place from testTbl group by name ,YEAR ,degree,place

as shown in pic 2 - I want to get the data for the first user and detailed description in their first year - cursor could make that but I didn't want to use it, and I think that there are many ways could handle the situation in the better way like cross apply or cte . I found a related question here Get top 1 row of each group but this doesn't work in my situation or I couldn't apply it so I made this new question here.

so i need to get the first row (top 1) from every group (user) and want to get the first record and must contains the first year that user works in it (order by desc)

回答1:

One of the possible solutions is:

select name, year, degree, place
from
(select 
    name, 
    year,
    degree,
    place,
    row_number() over (partition by name order by year) rn
from testTbl 
--group by name ,YEAR ,degree,place -- not really needed
) r
where rn = 1;

UPDATE: Another solution (since both I and Tim posted the same) would be to use CROSS APPLY:

select t.* 
from testTbl t
    cross apply (select top 1 id from testTbl where name = t.name order by year) r
where t.id = r.id

回答2:

Use ROW_NUMBER:

SELECT name, year, degree, place
FROM
(
    SELECT name, year, degree, place,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY year) rn
    FROM testTbl
) t
WHERE rn = 1;

If you wanted to consider the possibility of returning ties should, for example, a given name have two or more records with the same lowest year, there are a bunch of options. One would be to add other conditions to the ORDER BY clause used in the call to row number, to break the tie. Another option would be to use a rank function instead of row number, and return all ties.