Get items before and after an item - SQL Query
sql
Solution
Using a common table expression (the `WITH` part) 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
Normally in SQL there is no such concept as the previous or next items in a match. Actually, unless an order by clause is specified the rows are returned in any order that the sql engine find suitable. To make a query like this, an order has to be applied and index numbers generated. That's done in NumberedZipCodes. The second part is just a query to get the data out of it.
To have the query run efficiently, make sure that there is an index on the `ZipCode` column.
Problem
I am new to SQL, so I need your help on a query. Basically I have a Database of ZipCodes and I want to get 3 items before the selected ZipCode and 3 items after. The Query that 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 picked up a sample Query from somewhere and successfully converted it for my use. The only problem is that this Query returns current item and the next item. Instead, it should returns previous 3 items, current item, and next three items.