Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: How to write an sql statement involving not exists

    I am working on a homework assignment and the question is as follows:

    List the three most expensive products (one sql statement. Warning: HARD. Use NOT EXISTS)

    The product table has the following attributes:

    pid
    pname
    city
    quantity
    price

    If someone could help me with this it would be greatly appreciated.

    Kelly

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    1) We (TINW) don't do homework.
    2) It apears at best you can spell SQL
    3) You are less than resourceful & did not follow the advice in the #1 sticky post.

    http://asktom.oracle.com/pls/ask/f?p...A:276615773462
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    The easy way would be not to use EXISTS/NOT EXISTS at all:

    Code:
    SELECT pid, pname, price, price_ranking
    FROM   ( SELECT pid, pname, price
                  , RANK() OVER (ORDER BY price DESC) AS price_ranking
                  , DENSE_RANK() OVER (ORDER BY price DESC) AS price_ranking2
             FROM   products )
    WHERE  price_ranking <= 3;
    The NOT EXISTS approach will involve looking for products for which there are not three or more products with a higher price. There is going to be a COUNT in your NOT EXISTS subquery.

  4. #4
    Join Date
    Jan 2006
    Posts
    2
    Thank you William for the response it worked great.

Posting Permissions

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