Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Unanswered: For each value in one table, select nearest value in another table?

    This is pretty simple to describe:
    Table A & B both have a date field. I need a query/procedure which will do:
    SELECT A.date, { max(B.date) where B.date < A.date}
    Example:
    Code:
    A.date       B.date
    ======       ======
    10/10/2006   01/01/1999
    01/04/2007   12/12/2006
    
    Result:
    A.date       Max B.date < A.date
    ======       ===================
    10/10/2006   01/01/1999           // the biggest date in B which is < than 10/10/2006
    01/04/2007   12/12/2006
    So each row contains the date of an A, and the biggest date in B which is less than this value. It seems like it should be so simple but I just can't get it to work in a query, and I don't know the syntax Sybase use for writing procedures - if I were in Oracle I'd have given up and written a FOR loop by now... but a query would be neater anyway.

    Many thanks for any help.
    Last edited by d000hg; 08-08-07 at 06:09.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Try using a correlated sub query select a, (select b from...) from ...

  3. #3
    Join Date
    Aug 2007
    Posts
    3
    Quote Originally Posted by pdreyer
    Try using a correlated sub query select a, (select b from...) from ...
    Oh I've been trying sub-queries and so on, but I can't find a way to get it to work. I'm reasonably experienced with SQL but I'm a programmer who uses SQL, not a DB developer.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select A.date
         , max(B.date)
      from A
    inner
      join B
        on B.date < A.date
    group
        by A.date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    If you also need columns from table b use a sub query in your where clause e.g.
    Code:
    select o.id, o.name, o.crdate 
    , i.crdate icrdate, i.name iname
    from sysobjects o ,sysindexes i
    where o.id=i.id
      and o.crdate>='20070801'
      and i.crdate>o.crdate 
      and i.crdate=(select max(i2.crdate) 
                    from sysindexes i2 
                    where i2.id=o.id 
                      and i2.crdate>o.crdate)
    Last edited by pdreyer; 08-08-07 at 09:32.

Posting Permissions

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