Re: Subquery or Join?

This works on 11.50.xC6: CREATE TABLE TEST ( id INTEGER, dt DATETIME YEAR TO SECOND ) LOCK MODE ROW; INSERT INTO TEST VALUES ( 1, "2009-01-01 00:00:01"); INSERT INTO TEST VALUES ( 1, "2009-01-01 00:00:02"); INSERT INTO TEST VALUES ( 1, "2009-01-01 00:00:03"); SELECT t1.* FROM test t1, (SELECT FIRST 1 dt FROM test t2 WHERE t2.id = 1 ORDER BY DT DESC) t3 WHERE t1.id = 1 AND t1.dt = t3.dt Regards. On Wed, Mar 17, 2010 at 10:34 PM, J. Hart <unleashedmaniac@gmail.com> 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 > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...