From: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
To: pgsql-sql@postgresql.org
Subject: Re: Re: GROUPING and ORDERING and CONFUSION
Date: Thu, 20 Apr 2000 10:58:34 -0500
Message-ID: <20000420105834.B7013@rice.edu> (raw)
In-Reply-To: <NBBBLHOPPPPCHCIABAFGKELNCJAA.tim@celestialdesign.co.uk>
References: <NBBBLHOPPPPCHCIABAFGKELNCJAA.tim@celestialdesign.co.uk>
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., <reedstrm@rice.edu>
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..
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: reedstrm@wallace.ece.rice.edu
Subject: Re: GROUPING and ORDERING and CONFUSION
In-Reply-To: <20000420105834.B7013@rice.edu>
References: <NBBBLHOPPPPCHCIABAFGKELNCJAA.tim@celestialdesign.co.uk> <20000420105834.B7013@rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox