PetePuma
How many lumps do you want
Premium,MVM
join:2002-06-13
Arlington, VA | reply to dropshadow
Re: how to write SQL query
How do you determine what is the most recent invoice comment? Is there a timestamp column? Do you want the one with the highest identity? |
|
dropshadow
join:2000-12-17
Plano, TX
clubs: 
edit:
May 6th, @08:37PM
| sorry, yes, by timestamp...so something like comments.dateCreated or something, i don't have the db in front of me atm... |
|
PetePuma
How many lumps do you want
Premium,MVM
join:2002-06-13
Arlington, VA
| I'd probably do something like
I think that would work on DB2, it's been a while.
Disclaimer: done without syntax checking. |
|
dropshadow
join:2000-12-17
Plano, TX
clubs:
edit:
May 7th, @04:45PM
| thank you. your solution does indeed work, however it is slow. any way to speed this up? anyone? i want to avoid a subquery if possible, because that's slowing me down... |
|
jayco437
Premium
join:2001-08-11
Lincoln, NE
 ·Windstream
·AT&T Southwest
| I don't know anything about DB2, but my first thought if I was doing this in Postgres would to analyze the table and check the indexes.
Can you change the select max() to a select.... order by comments.date desc limit 1, and notice any improvement? |
|
Ray
Mahnahmahna
Premium
join:2001-04-02
Mesa, AZ
edit:
May 11th, @04:47PM
| That's what I was thinking. It's slow because that subselect is re-run for every row in the outer query. First, I'd create an index on the comment date if there isn't one already and see if that becomes acceptable.
Secondly, I'd try to make a query of the latest comment for each invoice a one-time deal as part of the FROM clause using a table alias. I'd need to create some test data & play with it for a while to actually give you some specific SQL, but something along the lines of
SELECT i.blah, c.comment
FROM invoices i
JOIN (SELECT most recent comment for each invoice) c
ON (c.invoice_id = i.invoice_id)
--
ON DELETE CASCADE |
|
