Re: SQL query

On Tue, 9 Jun 1998 11:32:17 -0700 (PDT), Jonathan Leffler <jleffler@informix.com> wrote: > >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. >> [snip some of my original posting] > >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). > Your right Jonathan. Richard's solution would not provide the correct solution...see my response below. >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. > [snip solution from Jonathan] The data I was interested in was indeed held in character fields. I couldn't be sure how many distinct values there would be in the live database, but I do know the values I want to treat as individuals and those that I want to group together. My example data was chosen to reflect how I knew that 001,002,003 and 004 were those I wanted to treat individually, and that 005, 006 and 007 were to be grouped together under the banner 'other'. I didn't want to expand on where this was leading to as it gets more complicated but resolving this issue would go a long way to helping me out. If things continue to get nasty with my report, I'll be back with more detail :-) Many thanks for the time and effort you have put into your response Jonathan. Best regards, Hamish.