Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    6

    Question Unanswered: Concept of this SQL query

    My SQL query is as follows
    Code:
    select title
    from book as B
    where (select count(*)
               from book as T
               where (T.price > B.price) < 5
    This query returns the 5 most expensive books. If I do >5, it is returning the 4 most expensive books. What is the concept behind this? Why is it so?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mahendrakariya View Post
    If I do >5, it is returning the 4 most expensive books.
    no, it does not
    Code:
    CREATE TABLE book
    ( title VARCHAR(99)
    , price INTEGER
    );
    INSERT INTO book VALUES
     ( 'book 1'       , 40 )
    ,( 'book two'     , 50 )
    ,( 'three'        , 30 )
    ,( 'book 5'       , 10 )
    ,( 'six     '     , 30 )
    ,( '7even'        , 40 )
    ,( 'eight'        , 15 )
    ,( 'book 9'       , 25 )
    ,( 'book 10'      , 35 )
    
    SELECT price,title FROM book ORDER BY price DESC;
    
    price	title
    50	book two
    40	book 1
    40	7even
    35	book 10
    30	three
    30	six
    25	book 9
    15	eight
    10	book 5
    
    select price,title
    from book as B
    where ( select count(*)
               from book as T
               where T.price > B.price ) < 5
    order by price desc
    
    price	title
    50	book two
    40	book 1
    40	7even
    35	book 10
    30	three
    30	six
    
    select price,title
    from book as B
    where ( select count(*)
               from book as T
               where T.price > B.price ) > 5
    order by price desc
    
    price	title
    25	book 9
    15	eight
    10	book 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    6
    no, it does not
    That is because some of the books have the same price. If we have different price for each book, the result is something different.

    Code:
    CREATE TABLE book
    ( title VARCHAR(99)
    , price INTEGER
    );
    
    INSERT INTO book VALUES
     ( 'book 1'       , 40 )
    ,( 'book two'     , 50 )
    ,( 'three'        , 60 )
    ,( 'book 5'       , 70 )
    ,( 'six     '     , 80 )
    ,( '7even'        , 90 )
    ,( 'eight'        , 105 )
    ,( 'book 9'       , 115 )
    ,( 'book 10'      , 125 )
    
    SELECT price,title FROM book ORDER BY price DESC;
    
    price	title
    125	book 10
    115	book 9
    105	eight
    90	7even
    80	six
    70	book 5
    60	three
    50	book two
    40	book1
    
    select price,title
    from book as B
    where ( select count(*)
               from book as T
               where T.price > B.price ) < 5
    order by price desc
    
    price	title
    125	book 10
    115	book 9
    105	eight
    90	7even
    80	six
    
    select price,title
    from book as B
    where ( select count(*)
               from book as T
               where T.price > B.price ) > 5
    order by price desc
    
    price	title
    60	three
    50	book two
    40	book1
    
    select price,title
    from book as B
    where ( select count(*)
               from book as T
               where T.price > B.price ) < 3
    order by price desc
    
    price	title
    125	book 10
    115	book 9
    105	eight
    
    select price,title
    from book as B
    where ( select count(*)
               from book as T
               where T.price > B.price ) > 3
    order by price desc
    
    price	title
    80	six
    70	book 5
    60	three
    50	book two
    40	book1

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mahendrakariya View Post
    select price,title
    from book as B
    where ( select count(*)
    from book as T
    where T.price > B.price ) > 5
    order by price desc

    price title
    60 three
    50 book two
    40 book1
    you have proved here, yourself, that your first post was incorrect

    in your first post, you said "If I do >5, it is returning the 4 most expensive books."

    clearly, 60-50-40 is ~not~ the 4 most expensive

    see what i mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •