Results 1 to 3 of 3

Thread: Group By Query

  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Unanswered: Group By Query

    Ok here's a very stupid question from an old man who is trying to learn SQL.
    I have a table which maintains some records about some books in a library.
    The columns are like: Book_No, Book_Name, Category, Cost
    Now if I want to get the details of the books with the highest price in their category, I am facing an issue.
    If I go with
    Code:
    select category, max(cost) from books group by category
    I get the proper output even though it doesnt give me the exact details of the books.
    But if I go for something like
    Code:
    select book_no, book_name, category, max(cost) from books
    group by category, book_no, book_name;
    the output goes all haywire.
    Can someone please explain why this is happening?
    I have found an alternate way of doing it with inline views but really not sure if I am doing the right thing here especially the where clauses.

    Code:
    select b.book_no, b.book_name, b.author_name, b.cost, inl.category from books b,
    (select category, max(cost) mcst from books group by category) inl
    where b.cost = inl.mcst
    and b.category = inl.category
    order by 4 desc;
    Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by sqleverest View Post
    Code:
    select book_no, book_name, category, max(cost) from books
    group by category, book_no, book_name;
    the output goes all haywire.
    Can someone please explain why this is happening?
    The max() is calculated for all columns in the group by clause. So you don't get the highest price per category, but the highest prices per unique combination of category/book_no/book_name which is - as book_no is the primary key - the same as the price for each book.

    I have found an alternate way of doing it with inline views but really not sure if I am doing the right thing here especially the where clauses.
    That is a perfectly fine solution.

    But as you posted this in the ANSI forum, there is another solution you can use with ANSI SQL. Try this one:
    Code:
    select *
    from (
       select book_no,
              book_name,
              category, 
              max(price) over (partition by category) as max_price,
              price as book_price
       from books
    ) t 
    where max_price = book_price
    The feature used here is called "windowing function" and lets you group your output without grouping the whole result. Nearly all modern DBMS support them nowadays.

    A nice introduction to them is available in the PostgreSQL manual: http://www.postgresql.org/docs/curre...al-window.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that is the correct way -- join to the inline view

    the WHERE clause method works, but it's better to use explicit JOIN syntax...
    Code:
    SELECT b.book_no
         , b.book_name
         , b.author_name
         , b.cost
         , inl.category 
      FROM ( SELECT category
                  , max(cost) AS mcst 
               FROM books 
             GROUP 
                 BY category ) AS inl
    INNER
      JOIN books AS b             
        ON b.category = inl.category
       AND b.cost = inl.mcst
    ORDER 
        BY b.cost DESC;
    also, it's better to use a column name rather than an ordinal position in the ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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
  •