PostgreSQL - Grouping specific columns in SQL

Question

The similar problem might have been raised here before, but it's very hard to find the answer i'm looking for.

Let's say I have a table like this:

     isbn      |       Name 1             | Name 2       | Name 3     |  Date
---------------+--------------------------+--------------+------------+------------
 9998-01-101-9 | Duomenu bazes            | Tadas        | Onaitis    | 1995-12-31
 9998-01-101-9 | Duomenu bazes            | Jonas        | Onaitis    | 1994-02-28
 9998-01-101-9 | Duomenu bazes            | Petras       | Jonaitis   | 1995-05-30
 9998-01-102-7 | Programavimo kalbos      | Petras       | Jonaitis   | 1995-05-30

I want to SELECT this table so that For Each Distinct ISBN and Name 1 there would be the Minimum DATE.

This could easily be achieved by using the MIN() aggregate function.

And by knowing that the columns ISBN and Name 1 are equivalent, we could easily GROUP BY these columns, and MIN(Date).

However, we have the columns Name 2 and Name 3, which are bound to Date.

And if we try to place them under the GROUP BY clause, they get checked while grouping, and we don't want that.

We want that Rows 1-3 would be One group, but they get separated, because Name 2 and Name 3 are making these rows distinct.

So how to make this so the Name 2 and Name 3 are not grouped, but instead bound to Date column and act as 1 column?

No new table creations or views aren't allowed.

Attempted solution: inner SELECT joining on Date from a different table, but this doesn't work if the Dates are duplicating.

Looks like a question related to OVER ( Partition by..) contructs available in Oracle — Massimo Petrus
– Massimo Petrus, Commented Feb 9, 2017 at 18:42

Gurwinder Singh · Accepted Answer · 2017-02-09 18:44:22Z

2

You can use built-in DISTINCT ON to get the row with earliest date for each combination isbn and name:

select distinct on (isbn, name_1) *
from your_table
order by isbn, name_1, date;

answered Feb 9, 2017 at 18:44

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Massimo Petrus · Accepted Answer · 2017-02-09 18:45:54Z

0

In oracle

    Select isbn, name1, name2, name3,date,min(date)OVER (PARTITION BY isbn, name1)

Maybe there are similar constructs in other rdbms

answered Feb 9, 2017 at 18:45

Massimo Petrus

1,9012 gold badges17 silver badges26 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Grouping specific columns in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related