DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: SQL Query MAX question

  1. #1
    Join Date
    May 2009
    Posts
    1

    SQL Query MAX question

    I'm writing an application using old Java EJB 2.0 queries, and I'm a bit stuck if there is a way to do what I need in a single query.

    Basically, the way I think of this is:

    SELECT DISTINCT OBJECT o WITH MAX(o.date) FROM Table AS o WHERE o.name = 'somePassedInName' AND o.date <= 'somePassedInTime'

    So I want to grab the record with the same name given, with the largest date that is less than the passed in date.

    I'm trying to avoid two queries, or adding logic to grab all the records and then find the one with the latest date, because the number of records returned to my application could be very large. Figured let Oracle do all that

    But the two queries would be something like:

    SELECT * FROM Table WHERE name = 'somePassedInName' and data <= 'somePassedInTime'

    and then from that get the one with MAX(date)

    And sorry if this is not the correct forum to post this in, just can't find anything in my searching to answer this

    Thanks for any help!

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Use ROW_NUMBER ()

    Try this:
    Code:
    SELECT a.*, ROW_NUMBER () OVER (ORDER BY DATA DESC) max_dt
      FROM tablea a
     WHERE NAME = 'somePassedInName'
       AND DATA <= 'somePassedInTime';
    And to get the MAX(DATA) select where max_dt = 1.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

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


Click Here to Expand Forum to Full Width