CODEWITHSUNDEEP
sqlpostgresql
Henry
Henry

Reputation: 1337

SQL how to limit number of a specific column?

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?

Upvotes: 2

Views: 3118

Answers (2)

eshirvana
eshirvana

Reputation: 24568

here is one way :

select * from (
   select * , dense_rank() over (order by author) rn
   from books
) t where t.rn < 11

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 0

Related Questions