Re: SQL query

On Tue, 9 Jun 1998, Richard Caldera wrote: > From what you've described and your sample data, it looks like you just > want the unique values in ascending order. > > Here's one method: > > select unique column1 > from table1 > order by column1;> > You can also use a "group by" clause, which is useful for aggregate > functions in your select statements. Hope this helps you out. > > At 14:25 6/8/98 GMT, Hamish McGregor wrote: > >I have a simple table containing just one column. > >Example test data unloaded to ascii file could be: > > > >001,001,002,002,002,003,004,005,006,006,007,007,007 > > > >What I want to do is issue a select statement that groups together the > >005, 006 and 007 as one such that I get the following output: > > > >001,002, 003, 004, 999 > > > >where 999 represents the others. > > > >I don't fancy a bunch of selects UNIONed togther as the big scale > >version of this would mean having a whopping select statment. I don't think that we have a sufficiently detailed description of the problem to be able to give a fully reliable answer. I think Hamish wants to be able to group all the values 005, 006 and 007 into a single group with a different identifier, so Richard's solution is not the answer (but I could be wrong). It isn't clear whether his data is integer or character fields; the leading zeroes could be either. It also isn't clear how he might want to handle groups in the 'big scale version'. For example, if the data only contains the seven distinct values shown, then all sorts of trivial methods can be devised. But if there are going to be 1000 values (000 .. 999) or more, then the trivial methods start flopping fast. I'm going to assume the data is integers, but the same code would adapt to CHAR fields with a suitable change of type. I think I'd explore a "partition table" such as the following: CREATE TABLE Partition ( Lower INTEGER NOT NULL, Upper INTEGER NOT NULL, Code INTEGER NOT NULL ); INSERT INTO Partition VALUES(1, 1, 1); INSERT INTO Partition VALUES(2, 2, 2); INSERT INTO Partition VALUES(3, 3, 3); INSERT INTO Partition VALUES(4, 4, 4); INSERT INTO Partition VALUES(5, 7, 999); Then we can do: SELECT DISTINCT P.Code FROM Partition P, Data D WHERE D.Value BETWEEN P.Lower AND P.Upper ORDER BY P.Code; This scales to handle any number of values and doesn't use monstrous unions. It may not be all that fast, especially if the data table is very repetitious, and you would probably do better to use: SELECT DISTINCT D.Value FROM Data D INTO TEMP TempData; SELECT DISTINCT P.Code FROM Partition P, TempData D WHERE D.Value BETWEEN P.Lower AND P.Upper ORDER BY P.Code; You can consider creating indexes on both the Partition table (highly recommended if it has more than the 5 rows above) and the temp table (and run update statistics on the temp table, too). Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix -- see http://www.perl.com/CPAN