Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2007
    Posts
    1

    Question Unanswered: select with max function

    Hi i have some problems with a select statement.

    I have a table like:

    tablea
    PK,FK id_request
    PK id_audit
    - user
    - date

    id_request is a FK and id_audit is an independent serial int for each id_request, a SELECT id_request,id_audit FROM tablea will look like this:

    id_request,id_audit
    1, 1
    1, 2
    1, 3
    2, 1
    3, 1
    3, 2
    etc...

    I need to get the row with the last id_audit for each id_request in a set of id_request.

    For only one id_request i used this query: select * from tablea where id_request=4874 AND id_audit IN (select max(id_audit) from tablea where id_request =4874 and it works fine

    But my problem comes when i need to get more than one. I tried something like this select * from tablea where id_request in(4874,1232) AND id_audit IN (select max(id_audit) from tablea where id_request(4874,1232)

    Of course it didn't work 'cause the single row return of the max(id_audit)function. I was triyng with group by and having but i can't get what i need.

    TYVM
    Last edited by alfredozn; 04-21-07 at 14:50.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    The solution with "GROUP BY" is probably the simplest one:
    Code:
    SELECT id_request, MAX(id_audit)
    FROM   tablea
    WHERE  id_request IN (...,...)
    GROUP BY id_request
    A "group by" is actually first "grouping", i.e., putting together, rows with same id_request, but then (and that's essential) "summarizing" each group by just returning a single row per group. In this case the "summary" is the max value for a certain column.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    peter, i have a feeling that additional columns will now be requested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's how I would do this with standardized SQL:
    Code:
    SELECT *
    FROM   tablea
    WHERE  ( id_request, id_audit ) IN ( SELECT id_request, MAX(id_audit)
                                         FROM   tablea
                                         GROUP BY id_request )
    Another idea would be:
    Code:
    SELECT b.*
    FROM   ( SELECT id_request, MAX(id_audit) AS max_id_audit
             FROM   tablea ) AS a JOIN
           tablea AS b ON a.id_request = b.id_request
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yet another idea:
    Code:
    select *
      from tablea as t
     where id_audit =
           ( select max(id_audit)
               from tablea 
              where id_request = t.id_request )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Just to correct something (at least I think it needs correcting) that stolze wrote in his second query :
    Code:
    SELECT b.*
    FROM   ( SELECT id_request, MAX(id_audit) AS max_id_audit
             FROM   tablea GROUP BY id_request ) AS a JOIN
           tablea AS b ON a.id_request = b.id_request

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Rudy's post is the correct result. Use "=" operator not IN.
    Just for clarification for others, what he has provided is a correlated subquery.
    i.e. for each row that is read it determines (via the subquery) if the row.id_audit is the largest id_audit number for that id_request number.
    So
    Code:
    row 1    (1,3)  => does 3  = MAX(id_audit) WHERE id_request = 1 ? NO!
    row 2    (1,4)  => does 4  = MAX(id_audit) WHERE id_request = 1 ? NO!
    row 3    (1,6)  => does 6  = MAX(id_audit) WHERE id_request = 1 ? YES, 6 is the maximum audit_id for id_request 1 ! => Add to resultset...
    row 4    (2,1)  => does 1  = MAX(id_audit) WHERE id_request = 2 ? NO!
    row 5    (2,2)  => does 2  = MAX(id_audit) WHERE id_request = 2 ? NO!
    row 6    (2,4)  => does 4  = MAX(id_audit) WHERE id_request = 2 ? YES! => Add to resultset...
    And thus we are returning multi rows...
    Last edited by aschk; 04-23-07 at 08:48.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just to correct something (at least I think it needs correcting) that aschk wrote in his correction of stolze's second query :
    Code:
    SELECT b.*
    FROM   ( SELECT id_request, MAX(id_audit) AS max_id_audit
             FROM   tablea GROUP BY id_request ) AS a JOIN
           tablea AS b ON a.id_request = b.id_request
                       AND a.max_id_audit = b.id_audit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    LOL, didn't pick up on that one

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aschk
    Rudy's post is the correct result. Use "=" operator not IN.
    actually, stolze's first result, using IN with row constructors, is correct

    remember which forum we're in
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Ah yes, whoops! So "=" doesn't work in SQL-92, and "IN" should be used ?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, could you repeat that last question?

    both "=" and "IN" work, you just have to use them properly!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    It's ok i'm with it now, I can see the ambiguity in what i wrote
    "IN" is used for comparision against multiple rows whereas "=" should be used for a single result. You CAN use "IN" for single row returns of course.

    Can you use "=" for a tuple comparison?

    i.e.
    Code:
    SELECT
    column1, column2
    FROM <table> t
    WHERE (column3,column4) = (
      SELECT id,name FROM <anothertable>
      WHERE column1=t.column1
    )

  14. #14
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I can answer my own question here
    If the subquery returns ONLY 1 result then a tuple comparision can be made, however (and this is going to be more likely the case, unless you're comparing MINs and MAXs) you get more than one result you need to use "IN".

    e.g. WORKS OK
    Code:
    SELECT
    column1, column2
    FROM <table> t
    WHERE (column3,column4) = (
      SELECT MIN(column3),MAX(column4) FROM <table>
    )
    e.g. DOESN'T WORK OK WITH MORE THAN ONE ROW RETURNED
    Code:
    SELECT
    column1, column2
    FROM <table> t
    WHERE (column3,column4) = (
      SELECT MIN(column3),column4 FROM <table> GROUP BY column4
    )
    Last edited by aschk; 04-23-07 at 09:55.

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by aschk
    Ah yes, whoops! So "=" doesn't work in SQL-92, and "IN" should be used ?
    The current SQL standard is SQL:2003. That allows row constructors (and "=") as well as uncorrelated subqueries using the IN operator.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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