query to extract below result

i have 2 tables ( dept and employee )

dept table structure

sno        deptname
1                hr
2                finance
3                accounts

employee table structure

empsno (pk )          deptsno( Fk)                   empname
1                                  1                                     a
2                                  1                                     b
3                                  1                                     c
4                                  1                                     d
5                                  2                                     e
6                                  2                                     fry
7                                  2                                     ghy
8                                  2                                     abc
9                                  3                                     yun
10                                3                                     john
11                                3                                     ppp
12                                3                                      xyz

i want to make query
to show record like this

this should be resuls after query executing

empsno deptsno empname

3 1 c

7 2 ghy
11 3 ppp

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

mohammad qasim12y ago
select A.empsno,A.deptsno,A.empname
from
(
select ET.empsno,ET.deptsno,ET.empname,Row_Number() OVER (partition by DT.deptsno ORDER BY ET.empsno) as No
from EmpTable ET
inner join DepTable DT on DT.deptsno=ET.deptsno
)A
where A.No=3
Ranjit Powar12y ago
Select empsno, deptsno, empname
from employee inner join
dept on employee.deptsno =dept.sno