Re: can my query be optmised

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joe Celko (joe.celko_at_northface.edu)
Date: 04/03/04 

Date: Fri, 02 Apr 2004 17:51:04 -0800

Here is one way to get rid of extrema in a numeric column. In many SQL
products, this is faster than you would think because the stats include
the min and max for columns.

CREATE TABLE Foobar(i INTEGER NOT NULL PRIMARY KEY);

SELECT F1.i
  FROM Foobar AS F1, Foobar AS F2
 GROUP BY F1.i
HAVING F1.i BETWEEN MIN(F2.i)+1 AND MAX(F2.i)-1;

Is that what you were trying to ask?

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Inserting a new PK into an existing table
    ... I want to add a new numeric column "id" into an existing table that is ... intended to be the new primary key. ... As I am quite new to SQL, I have also read a number of tutorials, ... UT1> select * from marktest; ...
    (comp.databases.oracle.misc)
  • Re: Inserting a new PK into an existing table
    ... I want to add a new numeric column "id" into an existing table that is ... intended to be the new primary key. ... As I am quite new to SQL, I have also read a number of tutorials, ... alter table yo add constraint pk_yo ...
    (comp.databases.oracle.misc)
  • Inserting a new PK into an existing table
    ... I want to add a new numeric column "id" into an existing table that is ... intended to be the new primary key. ... As I am quite new to SQL, I have also read a number of tutorials, ... Can it be done with "plain" SQL or do I need extensions like PL/SQL? ...
    (comp.databases.oracle.misc)