Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm relatively new to SQL and am looking for a command that I can run in order to find all records within a database within .5 of a calculated value.

For example:

SELECT X, (Y/Z) AS ZZ FROM Table WHERE ZZ WITHIN .5

However since WITHIN is not a valid SQL command, what's the actual command I can substitute in?

share|improve this question

3 Answers 3

It depends which vendor's SQL you are using, but, I would say it will be something along the lines of:

SELECT * FROM table WHERE column BETWEEN value1 AND value2

alternatively, you could use

SELECT * FROM table WHERE column <= value1 AND >= value2
share|improve this answer
    
This would work if there was a certain associated value I was interested in, but the idea is to output the records that are within .5 of each other, not some independent value. I'm using sqlite3 reference a sql database through Python. –  nrdk Nov 7 '11 at 14:38

Something like this (in SQL Server 2008 R2) what you have in mind?

CREATE TABLE Tab
(
    X FLOAT NOT NULL,
    Y FLOAT NOT NULL,
    Z FLOAT NOT NULL,
    ZZ AS Y / Z
);

INSERT INTO Tab
VALUES
( 1, 2, 3 ),
( 0.1, 0.2, 0.3 ),
( 0.1, 0.5, 1 ),
( 0.2, 0.4, 0.8 ),
( 0.5, 1, 1.5 );

SELECT X, Y, Z, ZZ
FROM Tab;

DECLARE @Delta FLOAT;
SET @Delta = 0.5;

SELECT X, Y, Z, ZZ, ZZ - @Delta, ZZ + @Delta
FROM Tab
WHERE X BETWEEN ZZ - @Delta AND ZZ + @Delta;
share|improve this answer
    
Modified to use computed column. Note that computed columns are not stored in the database unless declared as PERSISTED. –  BillP3rd Nov 7 '11 at 2:20

I think more directly you are looking for...

SELECT X, (Y/Z) AS ZZ FROM Table WHERE (Y/Z) BETWEEN (Y/Z) - .5 AND (Y/Z) + .5
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.