Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA37047 for ; Thu, 20 Apr 2000 11:58:35 -0400 (EDT) (envelope-from reedstrm@wallace.ece.rice.edu) Received: by wallace.ece.rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgresql.org; Thu, 20 Apr 2000 10:58:34 -0500 (CDT) Date: Thu, 20 Apr 2000 10:58:34 -0500 From: "Ross J. Reedstrom" To: pgsql-sql@postgresql.org Subject: Re: Re: GROUPING and ORDERING and CONFUSION Message-ID: <20000420105834.B7013@rice.edu> Mail-Followup-To: pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: ; from tim@celestialdesign.co.uk on Thu, Apr 20, 2000 at 02:28:01AM +0100 X-Archive-Number: 200004/224 Hmm, souynds like you want all the ruples returned, right? Then it's not really a GROUP problem, just an ORDER BY one. If all your data follows the example you gave, this should work: reedstrm=# select * from fred order by text(num); num | name --------+---------------------------------------- 15 | First Category Heading 1510 | first category subheading 1511 | second category subheading 1560 | spurious category subheading 156010 | first spurious category subheading 156011 | second spurious category subheading 16 | Second Category Heading 1610 | Second category subheading 161010 | Second category subheading subheading (9 rows) However, I _think_ your deeper question has to do with the indentation part, right? Are you trying to indent based on category level? If you've got some sort of programming language between your output and the SQL, then something like: reedstrm=# select text(num) as num ,length( text(num))/2 as level, name from fred order by text(num); num | level | name --------+-------+---------------------------------------- 15 | 1 | First Category Heading 1510 | 2 | first category subheading 1511 | 2 | second category subheading 1560 | 2 | spurious category subheading 156010 | 3 | first spurious category subheading 156011 | 3 | second spurious category subheading 16 | 1 | Second Category Heading 1610 | 2 | Second category subheading 161010 | 3 | Second category subheading subheading (9 rows) Then use the 'depth' to output the proper indentation level. Ross -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005 On Thu, Apr 20, 2000 at 02:28:01AM +0100, Tim Johnson wrote: > Hello, > > > My problem is I want to pull that data out and group it so that it comes out > just the way it went in.. So my page can read: > > 15: First Category Heading > 1510: first category subheading > 1511: second category subheading > 16: ... and so on.. > >