0

How can I selected rows from a table when a column data is different.

Table: id(pk), name, title, book_id, date

I want to get all the rows where book_id is uniq.

Data example:

test1, test2, 1, 2015-01-01
test2, test4, 1, 2015-01-02
test2, test3, 1, 2015-01-02
test2, test2, 4, 2015-01-03
test4, test2, 5, 2015-01-04
test5, test2, 5, 2015-01-05

Expected output:

test1, test2, 1, 2015-01-01
test2, test3, 1, 2015-01-02
test2, test2, 4, 2015-01-03
test4, test2, 5, 2015-01-04
1

This can easily be done using Postgres' distinct on operator:

select distinct on (book_id) name, title, bookd_id, date
from the_table
order by book_id, date;

This will pick the first row for each book_id based on the date column.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.