Re: Subquery or Join?

On Mar 17, 4:34 pm, "J. Hart" <unleashedman...@gmail.com> wrote: > On Mar 17, 3:48 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > > > > > J. Hart wrote: > > > I'm trying to query a table (lets call it "InvoicesTable") and I would > > > like to include the invoices last update remark by the employee whose > > > managing the invoice. In sql, I would probably do a subquery, using > > > the TOP function, but informix doesn't seem to allow you to use the > > > FIRST function within a subquery. > > > > In SQL, I would write it like: > > > select i.invoice_number, (select top 1 u.remarks from user_comments u > > > where u.invoice_number = i.invoice_number and u.employeeID = > > > i.invoiceOwnerID order by u.input_date desc) > > > from InvoicesTable i> > > > In informix, trying to use the same logic, I get the error "Cannot use > > > 'first' in this context." > > > > I also tried doing a join, but I couldn't figure out how to get the > > > last updated remark, it's just returning all remarks for that specific > > > user. > > > Sample Code > > > Select i.invoice_number, u.remarks > > > from InvoicesTable i > > > left join user_comments u on u.invoice_number = i.invoice_number and> > > u.employeeID = i.invoiceOwnerID > > > > Our informix database is a read only DR server. So, we are not able to > > > create stored procedures/functions or temp tables... It's a pain in > > > the neck dealing with the vendor and informix. > > > > Is this query possible or an I way off here? > > > > Thanks, > > > > James > > > You can include a where condition in the user_comments, like: > > > u.input_date = (select max(u1.input_date) from user_comments u1 where > > u1.invoice_number = i.invoice_number). > > > Efficiency is another matter... If performance becomes a problem, you > > can consider and index (invoice_number, input_date desc) on the > > user_comments table. > > > Regards.- Hide quoted text - > > > - Show quoted text - > > Well... I actually need to get the max u.input_date, u.input_time.... > > I actually took your suggestion and also used ROWID.  (If there is > anyone that used ROWID requestly, tell me if I'm using this > correctly)... > > Select i.invoice_number, u.remarks > from InvoicesTable i > left join user_comments u on u.invoice_number = i.invoice_number and> u.ROWID = (Select max(u1.ROWID) from user_comments u1 where > u1.invoice_number = i.invoice_number and u1.employeeID = > i.invoiceOwnerID) There is no guarantee that the maximum ROWID value is also the most recent record. Yours, Jonathan Leffler