select with max function

alfredozn · #1 (**permalink**) 04-21-07, 13:31

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

Peter.Vanroose · #2 (**permalink**) 04-21-07, 15:01

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.

r937 · #3 (**permalink**) 04-21-07, 15:35

peter, i have a feeling that additional columns will now be requested

stolze · #4 (**permalink**) 04-21-07, 15:47

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

r937 · #5 (**permalink**) 04-21-07, 15:49

yet another idea:

Code:

select *
  from tablea as t
 where id_audit =
       ( select max(id_audit)
           from tablea 
          where id_request = t.id_request )

aschk · #6 (**permalink**) 04-23-07, 07:36

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

aschk · #7 (**permalink**) 04-23-07, 07:37

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...

r937 · #8 (**permalink**) 04-23-07, 07:45

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

aschk · #9 (**permalink**) 04-23-07, 07:47

LOL, didn't pick up on that one

r937 · #10 (**permalink**) 04-23-07, 07:52

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

aschk · #11 (**permalink**) 04-23-07, 08:01

Ah yes, whoops! So "=" doesn't work in SQL-92, and "IN" should be used ?

r937 · #12 (**permalink**) 04-23-07, 08:03

sorry, could you repeat that last question?

both "=" and "IN" work, you just have to use them properly!!

aschk · #13 (**permalink**) 04-23-07, 08:49

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
)

aschk · #14 (**permalink**) 04-23-07, 08:50

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
)

stolze · #15 (**permalink**) 04-23-07, 12:34

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech