CODEWITHSUNDEEP
sqlselect
Yinks
Yinks

Reputation: 203

Extracting only one data in a table using select

I have 3 tables in my database which is

table 1 (users)
userid(PK)
EmployeeName

table 2 (SubDept)
SubDeptID(PK)
Department

table 3 (SubDeptTransfer)
TransferID(PK)
userid(FK)
SubDeptID(FK)

here is my example table for Table 3

enter image description here
what i wanted to do is to be able to print the SubDeptID of user 100. The problem is since there are two userid of 100 its printing both. the mission is to be able to print only one data with a latter TransferID. What could be the best select statement for the problem?

Upvotes: 0

Views: 59

Answers (2)

Nathan Stretch
Nathan Stretch

Reputation: 1038

I would do it like so:

SELECT subDeptId FROM SubDeptTransfer WHERE userId = 100 ORDER BY transferId DESC LIMIT 1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270091

The best way to do this is using the window function row_number():

select transferId, userId, subDeptId
from (select t.*,
             row_number() over (partition by userid order by TransferId desc) as seqnum
      from t
     ) t
where seqnum = 1

Upvotes: 2

Related Questions