Get X amount of latest entries with different attributes SQL Server -
let's have following table:
userid | fileid | version | date ------------------------------------- usera | filea | version1| 1.1.2016 usera | filea | version2| 2.1.2016 usera | filea | version3| 2.1.2016 usera | filea | version3| 3.1.2016 usera | filea | version3| 4.1.2016 usera | filea | version4| 5.1.2016 userb | filea | version2| 3.1.2016 and want latest 2 versions each user , file created before 4.1.2016, result should this:
userid | fileid | version | date ------------------------------------- usera | filea | version2| 2.1.2016 usera | filea | version3| 3.1.2016 userb | filea | version2| 3.1.2016 what correct sql statement result?
at moment, trying this
with findnewestversion ( select distinct date cdate, userid uid, fileid fid, version ver, row_number() on (partition userid, fileid, version order created desc)rn table created <= [date] ) select * table q inner join (select cdate, uid, fid, ver findnewestversion rn <= 2) x on q.userid = uid , q.date = cdate , q.fileid = fid , q.version = ver group q.userid, q.fileid, q.date, q.version but statement not quite correct, because returns result
userid | fileid | version | date ------------------------------------- usera | filea | version1| 1.1.2016 usera | filea | version2| 2.1.2016 usera | filea | version3| 2.1.2016 usera | filea | version3| 3.1.2016 userb | filea | version2| 3.1.2016 so, don't want first row (with version1) because there 2 other younger versions , don't want entry version3 2.1.2016 because there same entry 3.1.2016 closer entered date parameter
try this
attention: avoid culture-specific date formats!
i use cte add column set numbered each userid desc sorted date.
attention2: saw, 1 user might have 2 entries same date. might include version over(... order ...)
the real select cuts set numbered 1 or 2
set language german; declare @tbl table(userid varchar(100),fileid varchar(100),version varchar(100),[date] date); insert @tbl values ('usera','filea','version1','1.1.2016') ,('usera','filea','version2','2.1.2016') ,('usera','filea','version3','2.1.2016') ,('usera','filea','version3','3.1.2016') ,('usera','filea','version3','4.1.2016') ,('usera','filea','version4','5.1.2016') ,('userb','filea','version2','3.1.2016'); numbered ( select row_number() over(partition userid order [date] desc) sortnr ,* @tbl ) select * numbered sortnr<=2 the result
1 usera filea version4 2016-01-05 2 usera filea version3 2016-01-04 1 userb filea version2 2016-01-03
Comments
Post a Comment