Re: Limits

From: Roy Hann <specially_at_processed.almost.meat>
Date: Fri, 25 Jul 2008 10:22:16 -0500
Message-ID: <esWdnb35lcs1chTVnZ2dneKdnZydnZ2d_at_pipex.net>


JOG wrote:

>> SELECT * FROM parts p1
>> WHERE (SELECT COUNT(DISTINCT p2.unit_cost)
>> FROM parts p2
>> WHERE p2.unit_cost > p1.unit_cost) < 3
>
> Yes, that's effectively taking the same counting and grouping
> approach, written in a more compact way (although here you just have
> the top 3 and not a range, which would convlute the query
> somewhat...).

> However, what really jars me in your query though is its implict
> casting of a (nested) relation into an integer as part of the WHERE
> clause.

Scalar subqueries are considered perfectly respectable amongst SQL enthusiasts, but I confess they do stick in my throat and I have to master myself to stay in character when I am teaching this stuff.

I am a whore. I admit it.

-- 
Roy
Received on Fri Jul 25 2008 - 17:22:16 CEST

Original text of this message