June 1, 2004 at 9:15 pm
dear all :
I want select the records within 10
for example:
ID N1
---------------------
0006 5
0006 20
0006 21
0006 22
the Results is:
0006 5
0006 20
because 20-5>10
so select 0006 5
because 20,21,22 interval <10
so select only one record 0006 20
Any advice, links or pointers would be greatly appreciated.
Thanks a lot.
Vicky Cao
June 1, 2004 at 10:24 pm
Not sure how efficient this would be ... but hopefully better than a cursor...
CREATE TABLE [Test] (
[ID] [varchar] (5) NOT NULL ,
[N1] [integer] NOT NULL )
GO
Insert into Test (ID,N1) values(0006,5)
Insert into Test (ID,N1) values(0006,20)
Insert into Test (ID,N1) values(0006,21)
Insert into Test (ID,N1) values(0006,22)
Insert into Test (ID,N1) values(0006,56)
declare @WithinValue as integer
set @WithinValue = 10
select id,n1
from test as t1
where (t1.n1 + @WithinValue) < (select min(t2.n1) from test as t2 where t2.id = t1.id and t2.n1 > t1.n1) or
(t1.n1 - @WithinValue) > (select max(t2.n1) from test as t2 where t2.id = t1.id and t2.n1 < t1.n1)
June 1, 2004 at 11:23 pm
thank you for your reply.
but if delete records "0006,5" and "0006,56",
how can select "0006,20"
It's means
if
values(0006,5)
values(0006,20)
values(0006,21)
values(0006,22)
then
select values(0006,5)
values(0006,20)
if
values(0006,20)
values(0006,21)
values(0006,22)
then
select values(0006,20)
June 2, 2004 at 1:35 am
Using a temporary table to 1. get the records with gap of 10 between them , then add the minimum value for any records not existing in the table.
declare @WithinValue as integer
set @WithinValue = 10
create table #temptest (id varchar(5), n1 integer)
-- select values with gap of 10 between them
insert into dbo.#temptest (id,n1)
select id,n1
from test as t1
where
(t1.n1 + @WithinValue) < (select min(t2.n1) from test as t2 where t2.id = t1.id and t2.n1 > t1.n1) or
(t1.n1 - @WithinValue) > (select max(t2.n1) from test as t2 where t2.id = t1.id and t2.n1 < t1.n1)
order by id,n1
-- now insert any minimum values for ID's not already existing
insert into dbo.#temptest (id,n1)
select id,min(n1)
from test as t1
where id not in (select distinct id from #temptest)
group by id
select * from #temptest
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply