dotNETportal.cz

Korelačním poddotazem?

create table #test (machineID integer, [Time] datetime, counter integer)

go

insert into #test (machineId, [Time], counter) values (1,'2008-09-01 18:45:00',1)

insert into #test (machineId, [Time], counter) values (1,'2008-09-02 18:45:00',2)

insert into #test (machineId, [Time], counter) values (1,'2008-08-02 18:45:00',5)

insert into #test (machineId, [Time], counter) values (2,'2008-09-02 18:45:00',3)

insert into #test (machineId, [Time], counter) values (2,'2008-09-03 18:45:00',4)

insert into #test (machineId, [Time], counter) values (2,'2008-09-04 18:45:00',6)

select #test.machineId, #test.counter, #test.[TIME] from

(

select vyssi.counter from #test vyssi join #test nizsi on vyssi.machineID = nizsi.machineID

where vyssi.[Time] <= nizsi.[Time]

group by vyssi.counter

having count(*) <=1

) x

join #test on x.counter = #test.counter