Re: Q, UNIQUE/DISTINCT - GROUP/ORDER BY ASC < help wanted

>From: rjc@cbnewsf.cb.att.com (robert.cook) >Subject: Q, UNIQUE/DISTINCT - GROUP/ORDER BY ASC < help wanted >Date: Fri, 22 Apr 1994 19:03:37 GMT >X-Informix-List-Id: <news.6449> > >Question - If I select x fields with one defined as DISTINCT and GROUP/ORDER >BY in ASCending order, will I get the the oldest "ticket""version" record? >Given that the oldest version # is 5, would I get Ticket#.version 5 record? > >Also, how would I code this in an ACE report? > >Like > >SELECT Distinct(ticket), version, x, y, z >ORDER BY x, y, Distinct(ticket)> >ON EVERY ROW > PRINT Distinct(ticket), version, x, y, z > >AFTER GROUP of x > bla, bla bla > >or what? Unlike Informix 3.30, the DISTINCT keyword in SQL is a qualifier for the whole row, not for an individual column. Thus, your SELECT statement is the equivalent to: SELECT DISTINCT x, y, z, version, ticket ... ORDER BY x, y, ticket This means that you would get to see all the rows that appear, in sequence dictated by the ordering columns. If you want to see them in version order, use: ORDER BY x, y, ticket, version Now you print the detail data in the BEFORE GROUP OF ticket clause, and this guarantees to give you the earliest version, because you've sorted by version. If you don't sort by the column, you may, or may not, get the same results -- it depends on the query path chosen by the optimiser. The display label for the ticket column is "ticket" and is what you would refer to in the ACE report. This would be true of any computed column, but as noted earlier, ticket is not a computed column. Note: you can write a SELECT statement: SELECT x, y, z, distinct(ticket), version ... ORDER BY 1, 2, 4 This may or may not be accepted by ACE, but the only interpretation that can be given to that use of distinct is that there is a stored procedure called distinct which is to be executed with the argument ticket for each row returned by the query. SELECT x, y, z, distinct ticket, version ... ORDER BY 1, 2, 4 This is also legitimate syntax; it means select the column called distinct from one of the tables and give it the display label ticket. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>