Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Unanswered: Inner query problem

    consider a table book(title,price),where assuming that no two books have the same price,it's data is:
    title price
    a 23
    afraid 40
    b 10
    c 344
    t 90
    y 3455
    u 8888
    e 7888
    g 9999
    j 4567
    m 33111
    l 91909

    plz help in how this query produces title of five most expensive books

    select * from book as B where
    (
    select count(*) from
    book as T where T.price >B.price
    ) <5;


    regards
    puneet vyas

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you asking how it works?

    here you go --
    Code:
    SELECT B.title
         , B.price
         , ( SELECT COUNT(*) 
               FROM book AS T 
              WHERE T.price > B.price ) AS count_higher
      FROM book AS B 
    ORDER
        BY B.price DESC
    
    title   price   count_higher
    l       91909    0
    m       33111    1
    g        9999    2
    u        8888    3
    e        7888    4
    j        4567    5
    y        3455    6
    c         344    7
    t          90    8
    afraid     40    9
    a          23   10
    b          10   11
    as you can see, the "count_higher" column shows you exactly how many rows have a higher price

    the WHERE clause therefore works on the "count_higher" value and restricts the result to the five highest
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2010
    Posts
    2
    Thanks i got it ,
    regards
    puneet vyas

Tags for this Thread

Posting Permissions

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