title: Query to get the highest rank of each item
body: <p>I've the(simplified) following model:</p>
  
  <pre><code>Book
    id
    name
  
  BookCategory
    book_id
    category_id
    rank
  
  Category
    id
    name
  </code></pre>
  
  <p>With a given category id, I'd like to get the books having that category as the highest ranked one.</p>
  
  <p>I'll give an example to be more clear about it:</p>
  
  <pre><code>Book   
  
  id    name   
  ---  -------
  1     On Writing
  2     Zen teachings
  3     Siddharta
  
  BookCategory   
  
  book_id category_id      rank   
  ---       -------        -----
  1        2               34.32
  1        5               24.23
  1        9               54.65
  2        5               27.33
  2        9               28.32
  3        2               30.43
  3        5               27.87
  
  Category   
  
  id    name   
  ---  -------
  2     Writing
  5     Spiritual
  9     Buddism
  </code></pre>
  
  <p>The result for category_id = 2 would be the book with id = 3.</p>
  
  <p>This is the query I'm running:</p>
  
  <pre><code>SELECT book."name" AS bookname
  FROM bookcategory AS bookcat
  LEFT JOIN book ON bookcat."book_id" = book."id" 
  LEFT JOIN category cat ON bookcat."category_id" = cat."id" 
  WHERE cat."id" = 2
  ORDER BY bookcat."rank"
  </code></pre>
  
  <p>This is not the right way to do it because it doesn't select the max rank of each book. I've yet to find a proper solution.</p>
  
  <p>Note: I'm using the postgresql 9.1 version.</p>
  
  <p>Edit:</p>
  
  <p>DB Schema (taken from martin's SQL Fiddle answer):</p>
  
  <pre><code>create table Book (
    id int,
    name varchar(16)
    );
  
  insert into Book values(1, 'On Writing');
  insert into Book values(2, 'Zen teachings');
  insert into Book values(3, 'Siddharta');
  
  create table BookCategory (
    book_id int,
    category_id int,
    rank real
    );
  
  insert into BookCategory values(1,2,34.32);
  insert into BookCategory values(1,5,24.23);
  insert into BookCategory values(1,9,54.65);
  insert into BookCategory values(2,5,27.33);
  insert into BookCategory values(2,9,28.32);
  insert into BookCategory values(3,2,30.43);
  insert into BookCategory values(3,5,27.87);
  
  create table Category (
    id int,
    name varchar(16)
    );
  
  insert into Category values(2, 'Writing');
  insert into Category values(5,'Spiritual');
  insert into Category values(9,    'Buddism');
  </code></pre>