#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    1
    Rep Power
    0

    Relativley new to SQL, need help with query


    Hello, as the title says, I am fairly new to SQl. I am taking fundamentals of SQL at my college and we are using Oracle 10g express edition and I have this one homework problem that i can't figure out, nor does the book help...

    Basically the table is for a book company and it lists book title, book code, type, price, publisher code (not in that order) And this one question asks to query the title and price of the lowest priced book. I get to here:

    SELECT TITLE, MIN(PRICE)
    FROM BOOK;

    (BOOK is the table name) anyways, i know I am doing the title query wrong becuase in this sense I am asking for all the titles, but only 1 price so i know that doesn;t work...the problem is I am at a wall and can't figure out how to basically tell it to retrieve the title for that one book with the lowest price. This is the only problem on the whole hw that has made me say "i quit" lol. I'm not trying to cheat, I just don't have any sense of how to structure it to only get that one book's title. Evrything I have thought of still has the query structred so it is asking for all the titles, not one pertaining to the lowest priced book, so I'm just about lost (i'm not that fustrated lol....just reeeallly stuck).

    any help would be AWESOME.
    thanks everyone
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    386
    OK. So think about the logic of the question.

    This is the pseudo code you want
    SELECT TITLE, PRICE
    FROM BOOK
    WHERE PRICE = MIN(PRICE) IN BOOK

    I leave it to you to figure out how to turn that into proper SQL syntax.

    Also note that, the way you frame the question, you can have multiple titles returned.

    Clive
  4. #3
  5. No Profile Picture
    Choose Life...
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2006
    Location
    Scotland
    Posts
    459
    Rep Power
    516
    Could also use the "rownum" to select the top most record thats returned, which is also the smallest!

    Code:
    SELECT title, MIN(price)
    FROM book
    WHERE rownum=1
    GROUP BY title;

    Comments on this post

    • LKBrwn_DBA disagrees : rownum=1 will not guarantee lowest price
  6. #4
  7. No Profile Picture
    Choose Life...
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2006
    Location
    Scotland
    Posts
    459
    Rep Power
    516
    Originally Posted by scotland87
    Could also use the "rownum" to select the top most record thats returned, which is also the smallest!

    Code:
    SELECT title, MIN(price)
    FROM book
    WHERE rownum=1
    GROUP BY title;
    LK, could you explain why this wouldnt work? I always assumed that this would work. Im not being an arse or anything, it would just be good (for us learning) to have an explanation when a point is made. Appreciate it!

    Cheers
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    181
    Rep Power
    16
    Originally Posted by scotland87
    LK, could you explain why this wouldnt work? I always assumed that this would work. Im not being an arse or anything, it would just be good (for us learning) to have an explanation when a point is made. Appreciate it!

    Cheers
    Because the ROWNUM is really a pseudo-column that Oracle generates each time the query is run for the RESULTS
    of the query only, not the data in the table.

    So in order to find the book title with lowest price
    Code:
    select title, price
    from book
    where price = (select min(price) from book);
  10. #6
  11. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    123
    Originally Posted by scotland87
    LK, could you explain why this wouldnt work? I always assumed that this would work. Im not being an arse or anything, it would just be good (for us learning) to have an explanation when a point is made. Appreciate it!

    Cheers
    Just try adding a order by field desc clause to your query and check if that works !

IMN logo majestic logo threadwatch logo seochat tools logo