dropshadow
join:2000-12-17
Plano, TX
clubs: 
 ·Verizon FIOS
| how to write SQL query
i have a table with invoices. i have another table with invoice comments. what i want to do is perform a join, but only retrieve the most recent comment for each invoice.
basically, what i would like to do is this:
select invoices.invoiceNumber, invoices.invoiceAmount, TOP 1 comments.invoiceComment, from invoices inner join comments on invoices.invoiceNumber = comments.invoiceNumber.
however, my db does not seem to like this syntax. i am on an IBM DB2. it basically understands standard SQL. is there another way to write this statement? what i do not want retrieved is multiple lines of invoices since there are sometimes many comments per invoice. |
|
PetePuma
How many lumps do you want
Premium,MVM
join:2002-06-13
Arlington, VA | 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
El Paso, TX
 ·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 |
|
