-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

iandrepont
  • 7
  • 4
  • Oracle has several different ways of solving this problem, with later versions offering more choice of implementation. The linked thread covers all the variations. – APC Apr 06 '19 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. – iandrepont Apr 06 '19 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. – iandrepont Apr 06 '19 at 20:01

2 Answers2

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
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • 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. – iandrepont Apr 06 '19 at 19:20
  • Please ask another question with correct details if that's the case. – Kaushik Nayak Apr 06 '19 at 19:24
  • It won't let me ask another question.. How could I phrase it better? because it seems like several people misunderstood. – iandrepont Apr 06 '19 at 19:29
  • @iandrepont : see edits – Kaushik Nayak Apr 06 '19 at 19:42
  • @iandrepont - it's not so much that *" several people misunderstood"* as that you didn't really explain your requirement clearly the first time around. – APC Apr 06 '19 at 20:34
  • @Kaushik Nayak - Awesome thanks. Though it is giving me an error with the "fetch first 1 rows only" part. It says SQL command not entered properly. I'm currently trying to find a solution, but if you know what it could be that would be great to know as well. – iandrepont Apr 06 '19 at 20:43
  • @iandrepont : check my 2nd edit, you're using an older Oracle version. As I mentioned in the first line of my answer, the `fetch first` was only for versions Oracle 12c and above. – Kaushik Nayak Apr 07 '19 at 06:44
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.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110