I have a table (books) like this:
id | author | book
--------------
1, Joy, book1
2, Joy, book2
3, Bob, book3
4, Bob, book4
5, Bob, book5
6, Dan, book6
...
I need a query to get 10 authors with their books. The below select is NOT what I want:
SELECT author, book
FROM books
LIMIT 10
How can I limit the result in 10 authors?
here is one way :
select * from (
select * , dense_rank() over (order by author) rn
from books
) t where t.rn < 11
As a note: If these are your only columns, then you might consider aggregation:
select author, array_agg(book)
from books
group by author
limit 10;
This returns 10 rows rather than a variable number of rows.