Need Query Help

I'm need a query that takes the number from the identity column, then uses
that for the next routine in a range...something like;
Select IdentityNumber
From TableName
Where LastName = 'Somebody'
(Then it takes that IdentityNumber, say row 100, an uses it to grab the rows
on both sides of 100, say 20 rows in each direction. So the second 1/2
of the operation would look like this.
Select FirstName, Lastname, Address
From TableName
Where identitynumbe(100) minus 20 rows and Idenittynumber(100) plus 20 rows.
Thanks in advance
JeffDefine "direction". Rows are not stored in any particular order. Specify a
criteria to sort the data, pot the DDL and some sample data.
ML|||SELECT b.FirstName, b.LastName, b.Address
FROM TableName a JOIN TableName b
ON b.IdentityNumber
BETWEEN (a.IdentityNumber -20) AND (a.IdentityNumber + 20)
WHERE a.LastName = 'Somebody'|||Try something like the following:
select b.FirstName, b.Lastname, b.Address
from TableName a
join TableName b on b.IdentityNumber between a.IdentityNumber - 20 and
a.IdentityNumber + 20
where a.LastName = 'Somebody'
--Brian
(Please reply to the newsgroups only.)
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:F46D2C51-A7CB-45B7-A29B-3A297DF00F98@.microsoft.com...
> I'm need a query that takes the number from the identity column, then uses
> that for the next routine in a range...something like;
> Select IdentityNumber
> From TableName
> Where LastName = 'Somebody'
> (Then it takes that IdentityNumber, say row 100, an uses it to grab the
> rows
> on both sides of 100, say 20 rows in each direction. So the second 1/2
> of the operation would look like this.
> Select FirstName, Lastname, Address
> From TableName
> Where identitynumbe(100) minus 20 rows and Idenittynumber(100) plus 20
> rows.
>
> Thanks in advance
> Jeff|||create table Names (
IdentityNumber int,
LastName varchar(255),
FirstName varchar (255)
LogTime datetime, service varchar( 255), machine varchar( 255)
)
87, Peters, Henry, 08/17/2005 10:14:00.000
88, Smith, John, 08/17/2005 10:15:00.000
89, Johnson, Sally, 08/17/2005 10:16:00.000
90, Harris, Betty, 08/17/2005 10:17:00.000
91, Thomas, Steve, 08/17/2005 10:17:30.00
So the query would search and find Johnson,
but return say 20 rows before sally Johnson
(rows 68-88) and 20 rows after Sally Johnson
(rows 90-110)
Thanks Again!!
"ML" wrote:

> Define "direction". Rows are not stored in any particular order. Specify a
> criteria to sort the data, pot the DDL and some sample data.
>
> ML|||>> So the query would search and find Johnson, but return say 20 rows before
Generate a rank column based on the whichever column you want to use to
sequence your data and then use it in your WHERE clause. There are several
ways you can write this & here is one with a derived table construct using
the datetime column used for sequencing :
SELECT t1.*
FROM tbl t1, ( SELECT rank - 20, rank + 20
FROM ( SELECT t1.fname, COUNT( * )
FROM tbl t1, tbl t2
WHERE t2.dt <= t1.dt
GROUP BY t1.id, t1.fname, t1.lname, t1.dt
) T ( fname, rank )
WHERE fname = 'Johnson' ) D ( r1, r2 )
WHERE ( SELECT COUNT( * )
FROM tbl t2
WHERE t2.dt <= t1.dt ) BETWEEN r1 AND r2 ;
A view could give you a easier read like:
CREATE VIEW vw ( id, fname, lname, dt, rank ) AS
SELECT t1.id, t1.fname, t1.lname, t1.dt, COUNT( * )
FROM tbl t1, tbl t2
WHERE t2.dt <= t1.dt
GROUP BY t1.id, t1.fname, t1.lname, t1.dt
Now, the query is simpler:
SELECT *
FROM vw v1, vw v2
WHERE v1.rank BETWEEN v2.rank - 2 AND v2.rank + 2
AND v2.fname = 'Johnson' ;
Anith|||On Thu, 18 Aug 2005 08:20:03 -0700, Jeff wrote:

>create table Names (
>IdentityNumber int,
>LastName varchar(255),
>FirstName varchar (255)
>LogTime datetime, service varchar( 255), machine varchar( 255)
> )
>87, Peters, Henry, 08/17/2005 10:14:00.000
>88, Smith, John, 08/17/2005 10:15:00.000
>89, Johnson, Sally, 08/17/2005 10:16:00.000
>90, Harris, Betty, 08/17/2005 10:17:00.000
>91, Thomas, Steve, 08/17/2005 10:17:30.00
>So the query would search and find Johnson,
>but return say 20 rows before sally Johnson
>(rows 68-88) and 20 rows after Sally Johnson
>(rows 90-110)
>Thanks Again!!
Hi Jeff,
Untested (since you didn't post the sample data as INSERT statements):
SELECT n1.IdentityNumber, n1.LastName, n1.FirstName, n1.LogTime
FROM Names AS n1
WHERE EXISTS
(SELECT *
FROM Names AS n2
WHERE n2.LastName = 'Johnson'
AND n1.IdentityNumber BETWEEN n2.IdentityNumber - 20
AND n2.IdentityNumber + 20)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)