Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .