Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Posts
    2

    Question Unanswered: simple SQL query

    Hi PPL,

    i need help for following problem.
    my table (actual date is 2007/sysdate):

    name | date_from | date_to | Version
    ================================================== ===
    N1 2000 2008 1
    N1 2002 2008 2 <---------
    N1 2008 2010 3
    N2 2001 2002 1
    N2 2002 2010 2 <---------

    id like to get all sets with:
    1. actual date is between inside data_from and date_to
    AND
    2. highest version

    so, the result for my table is (every name occurs once!):
    N1 2002 2008 2
    N2 2002 2010 2

    Has anybody an idea for a SQL query??
    Thanks!
    Mike

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select name
         , date_from
         , date_to
         , Version
      from daTable as T
     where Version = 
           ( select max(Version)
               from daTable
              where name = T.name
                and year(current_date)
                    between date_from and date_to )
    assumes Version is distinct within name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    2

    thanks

    hi r937,

    it works very fine but I should describe my problem otherwise :

    the result of:
    1. actual date is between inside data_from and date_to
    example:
    [name] [date_from] [date_to] [version]
    a_______2001_______2010_____1____
    a_______2002_______2008_____9____
    b_______2000_______2009_____1____


    should be used in:
    2. highest version
    end result:
    [name] [date_from] [date_to] [version]
    a_______2002_______2008_____9____
    b_______2000_______2009_____1____

    So we have two steps while the first one has higher prioraty.
    PS: I use Oracle9.2

    Thanks
    Mike

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    two things:

    first, if you wanted an oracle solution, you should've posted in the oracle forum, duh

    second, did you try my query at all?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Your step 1 is implemented by the WHERE condition of r937's subquery.
    Your step 2 is implemented by the MAX expression in the SELECT subclause of that subquery, which is effectively executed after the WHERE subclause.
    Finally, there is a step 3, viz. the retrieval of the full lines that correspond to the combined [name],[MAX(version)] of the subquery.
    This is done in the outer query.
    Last edited by Peter.Vanroose; 01-18-07 at 12:30.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •