Geek asks and answers

Get items before and after an item - SQL query

I am new to SQL, so I need your help on request. I basically have a ZipCodes database and I want to get 3 elements before the selected ZipCode and 3 elements after. The query I came up with is pretty bad ...

WITH numberedlogtable AS
(
SELECT *
 FROM dbo.US
)

SELECT *
FROM numberedlogtable
WHERE ZipCode IN (SELECT ZipCode+i
             FROM numberedlogtable
             CROSS JOIN (SELECT -1 AS i UNION ALL SELECT 0 UNION ALL SELECT 1) n
             WHERE ZipCode='91803')

I took an example request from somewhere and successfully converted it for my use. The only problem is that this query returns the current item and the next item. Instead, it should return the previous 3 elements, the current element, and the next three elements.

+3
source share
3 answers

Using a common table expression (part WITH) producing a numbered sequence:

WITH NumberedZipCodes AS
(SELECT SELECT ROW_NUMBER() OVER (ORDER BY ZipCode) AS RowNumber, *
FROM ZipCodes)

SELECT * From NumberedZipCodes
WHERE RowNumber BETWEEN
(SELECT RowNumber FROM NumberedZipCodes WHERE ZipCode=91803) - 3
AND (SELECT RowNumber FROM NumberedPerson WHERE ZipCode=91803) + 3

SQL , . , order by, , sql . , . NumberedZipCodes. - , .

, , ZipCode .

+5

"Before" "after" . , , 2 , :

SELECT TOP(3) *
FROM numberedlogtable
WHERE ZipCode >= '91803'
ORDER BY ZipCode

:

SELECT TOP(3) *
FROM numberedlogtable
WHERE ZipCode < '91803'
ORDER BY ZipCode DESC

UNION ALL , , .

SQL Fiddle.

+4

3 ZipCode 3 afte

SQL , , ( ).

- FURST , . 6. , . x + 3 Top 4 .., 3- .

, /, * ZipCodes * , (a) " ", .

.

0

More articles:


All Articles