up vote 0 down vote favorite

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.

share|improve this question
    
Looks like a question related to OVER ( Partition by..) contructs available in Oracle – Massimo Feb 9 at 18:42
    
I could make use of a version for Sybase ASE 15.7 – Horaciux Feb 9 at 19:15
up vote 2 down vote accepted

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;
share|improve this answer
up vote 0 down vote

In oracle 

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

Maybe there are similar constructs in other rdbms

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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