1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
|
drop table test;
go
create table test
(
dte date not null,
dos_id int not null,
status_code int not null
);
go
alter table test
add constraint uc_date_dos unique (dte, dos_id);
go
insert into test (dte, dos_id, status_code) values ('2012-05-03', 456, 20);
insert into test (dte, dos_id, status_code) values ('2012-12-13', 456, 30);
insert into test (dte, dos_id, status_code) values ('2014-05-15', 456, 50);
insert into test (dte, dos_id, status_code) values ('2013-05-21', 456, 80);
insert into test (dte, dos_id, status_code) values ('2011-05-23', 789, 90);
insert into test (dte, dos_id, status_code) values ('2012-07-23', 789, 30);
insert into test (dte, dos_id, status_code) values ('2013-11-26', 352, 80);
insert into test (dte, dos_id, status_code) values ('2012-12-23', 352, 90);
insert into test (dte, dos_id, status_code) values ('2011-06-09', 352, 50);
select t2.dte, t2.dos_id, t2.status_code
from test t2
inner join (
select max(t1.dte) dte, t1.dos_id
from test t1
group by dos_id
) tmp on tmp.dte = t2.dte and tmp.dos_id = t2.dos_id; |
Partager