How to get data from two tables in sql

Question

I have two tables User and UserFiles as shown below UserId Name EMaIL Password Phone 1 First a@a.com 12346456 456456 2 second b@b.com 111235 23235 User Files table Below RecordId(primary) UserID(Foreign) FileName PublihsedBy Date 1 1 First.DOc A 1/1/2001 2 1 second.doc B 1/1/2012 3 1 Third.Doc B 1/1/2016 4 2 G.PDF B 1/1/2014 as you can see against one user id we have multiple files like for userid=1 we have three files I want to get the record with latest file inserted in our case i want to get the Third.Doc how to achieve that

Bikesh Srivastava · Answer

You can use inner join with order by asc

Ananth G · Answer

SELECT a. Name b.FileName FROM User_tablesa LEFT JOIN ( SELECT FileName ROW_NUMBER()over(PARTITION BY UserID ORDER BY DATE DESC ) AS Row_No FROM User_Files_table )b ON a.UserId=b.UserID WHERE b.Row_No= '1' it help to u

Jisny Av · Answer

select * from [user] u inner join ( select useridfilenamepublihsedby daterow_number()over (partition by userid order by convert(varchar(50)cast(date as datetime)102) desc) as rno from userfiles) f on u.userid=f.userid where f.rno=1

Shakti Singh Dulawat · Answer

1) Join both table based on User ID 2) Set order by from Date and Pick top one using Select Top 1

UserId	Name	EMaIL	Password	Phone
1	First	[email protected]	12346456	456456
2	second	[email protected]	111235	23235

RecordId(primary)	UserID(Foreign)	FileName	PublihsedBy	Date
1	1	First.DOc	A	1/1/2001
2	1	second.doc	B	1/1/2012
3	1	Third.Doc	B	1/1/2016
4	2	G.PDF	B	1/1/2014