-1

I have a database where there are bands in one column and another column where there are release dates of their albums, how do i find the oldest band based off of the difference between their release dates? I.e. the oldest band is not Band1 because they first released an album in 1968, with my method Band1 is only 7 years old (or active for 7 years could be better), while Band2 is 26 years old (years active) because 2001 - 1975 = 26.

For instance:

Band......Rel_date

Band1....12/12/68

Band1....08/05/75

Band2....09/02/75

Band2....05/05/99

Band2....03/05/01

Improve this question
3
  • Oracle has several different ways of solving this problem, with later versions offering more choice of implementation. The linked thread covers all the variations. Commented Apr 6, 2019 at 18:36
  • I don't see how that answers my question. I'm asking how do i find the oldest, not from just finding the oldest value, but finding the oldest by finding the largest amount of time between most recent album and first album. Commented Apr 6, 2019 at 19:11
  • I guess time active would be better wording because I'm trying to find the band with the most amount of time between their oldest and most recent albums. Commented Apr 6, 2019 at 20:01

2 Answers 2

Reset to default
1

Oracle 12c+

select band from your_table order by rel_date fetch first 1 rows only;

If you want to include ties, change it to with ties instead of only

EDIT

select band from t group by band order by max(rel_date) - min(rel_date) desc fetch first 1 rows only;

EDIT2

For Oracle versions before 12c:

select * from ( select band from t group by band order by max(rel_date) - min(rel_date) desc ) where rownum = 1
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks! But I misphrased my question. I'm asking how would I figure out how many years old a band is by taking their most recent album release and subtracting their first album release to find how many years they have been active.
Please ask another question with correct details if that's the case.
It won't let me ask another question.. How could I phrase it better? because it seems like several people misunderstood.
@iandrepont : see edits
@iandrepont - it's not so much that " several people misunderstood" as that you didn't really explain your requirement clearly the first time around.
|
0

If I understand what you're asking

SELECT BAND FROM YOUR_TABLE WHERE REL_DATE = (SELECT MIN(REL_DATE) FROM YOUR_TABLE)

oughta do it.

EDIT

Based on further information

WITH cteBand_activity AS (SELECT BAND, MIN(RELEASE_DATE) AS FIRST_RELEASE_DATE, MAX(RELEASE_DATE) AS LATEST_RELEASE_DATE, TRUNC((MAX(RELEASE_DATE) - MIN(RELEASE_DATE)) / 365) + 1 AS YEARS_ACTIVE FROM ALBUMS GROUP BY BAND ORDER BY YEARS_ACTIVE DESC) SELECT BAND, YEARS_ACTIVE FROM cteBand_activity WHERE ROWNUM = 1

should give the result you're looking for.

dbfiddle here

Best of luck.

Improve this answer

2 Comments

Thanks! But I misphrased my question. I'm asking how would I figure out how many years old a band is by taking their most recent album release and subtracting their first album release to find their age. (maybe years active could be a better term to describe it)
Writing good questions takes practice. Keep at it.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.