Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008

    Unanswered: Simple query - help plz


    I have a current query that returns me a number of lines but I need to return only the next closest date, not too sure how to do this neatly,

    select * from table1 where status='nice' and sysdate < datcar

    this returns me:

    numid // status //colour // date
    5 nice red 04/04/09
    6 nice blue 04/04/10
    7 nice green 04/04/11
    8 nice red 04/04/12

    I will need to have the first query only where numid=5.
    the closest date to us will always have the minimum numid...

    Many Thanks for your help!!!

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    You might try to use MIN or MAX function with the date column, such as
    select some_columns
    from some_table t
    where t.date_column = (select max(t1.date_column) 
                           from some_table t1
                           where t1.maybe_some_id = t.maybe_some_id
                             and t1.date_column <= sysdate

  3. #3
    Join Date
    Dec 2003
    You could also try the following, but you should compare it to the previous suggestion in terms of which is more efficient for your environment (plus, this example only returns 1 record, while the previous example is multi-purpose ... it works for 1 record, or for many):

    select * 
    from (
      select * 
      from table1 
      where status='nice' and sysdate < datcar
      order by datcar)
    and rownum < 2;
    Last edited by chuck_forbes; 03-30-09 at 12:35.

Posting Permissions

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