1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to get this?

Discussion in 'SQL PL/SQL' started by Subu_bala89, Dec 7, 2017.

  1. Subu_bala89

    Subu_bala89 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Chennai
    Name start_year end_year
    Swift 2007 2009
    Swift 2009 2011
    Swift 2011 2013
    Maruti 2009 2011
    Maruti 2013. 2015

    Write a query for the above mentioned table for the output to be
    Output:
    Name Start-year. End-year
    Swift. 2007 2013
    Maruthi. 2009 2011
    Maruthi. 2013 2015
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,599
    Likes Received:
    363
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What have you written to solve this? Post your work and we'll help you; we won't do your work for you.
     
  3. Subu_bala89

    Subu_bala89 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Chennai
    Got it.
    select name,min(start_year),max(end_year)from cars where name='swift' group by name
    union
    select name,start_year,end_year from cars where name<>'swift'
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    770
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Why here in your query use UNION , but no UNION ALL?
     
  5. vibhasss

    vibhasss Active Member

    Messages:
    7
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    India
    suppose we add

    Honda 2006 2007
    Honda 2007 2009
    Honda 2011 2012
    Honda 2012 2014
    Honda 2016 2017


    Below query work in all scenarios:

    select
    distinct name,start_yr,end_yr
    from (
    select name,start_year,end_year,decode(has_parent,0,start_year,lag (start_year,1) OVER (PARTITION BY name ORDER BY start_year)) start_yr, decode(has_child,0,end_year,(LEAD (end_year,1) OVER (PARTITION BY name ORDER BY start_year))
    ) end_yr from (
    select
    name,start_year,end_year,prev_start_yr,next_start_yr,prev_end_yr,next_end_yr,
    (case when (end_year=next_start_yr) then 1 else 0 end ) as has_child,
    (case when (start_year=prev_end_yr) then 1 else 0 end ) as has_parent
    from(
    SELECT name,start_year,end_year,
    lag (start_year,1) OVER (PARTITION BY name ORDER BY start_year) prev_start_yr,
    LEAD (start_year,1) OVER (PARTITION BY name ORDER BY start_year) next_start_yr,
    lag (end_year,1) OVER (PARTITION BY name ORDER BY start_year) prev_end_yr,
    LEAD (end_year,1) OVER (PARTITION BY name ORDER BY start_year) next_end_yr
    FROM cars
    order by name
    )
    )where has_child =0 or has_parent =0
    )
    ;
     
    Last edited: Dec 23, 2017