Need a sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need a sql query

Author

Topic

vickycao
Starting Member

2 Posts

Posted - 2004-06-01 : 23:09:44

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-01 : 23:36:20

This is rough, but:

DECLARE @test TABLE(ID CHAR(4), N1 INT)

INSERT @test(ID, N1)
SELECT 0006,5
UNION ALL
SELECT 0006,20
UNION ALL
SELECT 0006,21
UNION ALL
SELECT 0006,22

DECLARE
@searchnumber INT,
@interval INT

SELECT
@searchnumber = 20,
@interval = 10

SELECT
ID,
N1
FROM
@test
WHERE
N1 BETWEEN @searchnumber AND (@searchnumber-(@interval+1))
OR N1 BETWEEN @searchnumber AND (@searchnumber+(@interval-1))

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

vickycao
Starting Member

2 Posts

Posted - 2004-06-02 : 00:19:44

thank you for your reply.

but why need @searchnumber param ?

if use cursor can solve this problem.
means current record N1 - previous Record N1 >10
then select current
else ignore .

but how can I do this without use cursor.

thanks a lot .

vickycao

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-06-02 : 07:10:37

Have a look at the following....http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=17768&SearchTerms=sequence

you may be able to adapt it to spot gaps....which match your target criteria.

One issue you have to recognise is that "logically" you must treat the input set as having NO ORDER on the data....unless you EXPLICITLY put one on it...

It is entirely valid for the SQL engine to process the input data set as IF it was in the following sequence
ID N1
---------------------
0006 5
0006 21
0006 20
0006 22

and then as
ID N1
---------------------
0006 5
0006 22
0006 20
0006 21

in 2 consecutative runs...(which will affect your output/result set)...UNLESS you EXPLICITY ORDER the input data.....This arguement/point has been explored here many times.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-02 : 08:50:06

I'm going to go beat my head against a wall.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources