pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: GROUPING and ORDERING and CONFUSION
3+ messages / 3 participants
[nested] [flat]
* Re: GROUPING and ORDERING and CONFUSION
@ 2000-04-20 01:28 Tim Johnson <tim@celestialdesign.co.uk>
2000-04-20 09:21 ` Re: Re: GROUPING and ORDERING and CONFUSION Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
2000-04-20 15:58 ` Re: Re: GROUPING and ORDERING and CONFUSION Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 2 replies; 3+ messages in thread
From: Tim Johnson @ 2000-04-20 01:28 UTC (permalink / raw)
To: pgsql-sql
Hello,
I was hoping somebody might be able to help me do this: I have a table with
two fields (num int, name text)
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
and so on...
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..
I thought I could probably group by num on the first two digits and order by
asc? but I can't find a way to make that work.
Thanks in advance.
Tim Johnson
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Re: GROUPING and ORDERING and CONFUSION
2000-04-20 01:28 Re: GROUPING and ORDERING and CONFUSION Tim Johnson <tim@celestialdesign.co.uk>
@ 2000-04-20 09:21 ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
1 sibling, 0 replies; 3+ messages in thread
From: Moray McConnachie @ 2000-04-20 09:21 UTC (permalink / raw)
To: tim@celestialdesign.co.uk; pgsql-sql
If it was literally the way the data went in, you should be able to order by
oid. Alternatively,
SELECT * FROM tablename ORDER BY substring (text(num) from 1 for 2);
should do it.
----------------------------------------------------------------
Moray.McConnachie@computing-services.oxford.ac.uk
----- Original Message -----
From: "Tim Johnson" <tim@celestialdesign.co.uk>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, April 20, 2000 2:28 AM
Subject: [SQL] Re: GROUPING and ORDERING and CONFUSION
> Hello,
>
> I was hoping somebody might be able to help me do this: I have a table
with
> two fields (num int, name text)
>
> 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
>
> and so on...
>
> 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..
>
>
>
> I thought I could probably group by num on the first two digits and order
by
> asc? but I can't find a way to make that work.
>
> Thanks in advance.
>
>
> Tim Johnson
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Re: GROUPING and ORDERING and CONFUSION
2000-04-20 01:28 Re: GROUPING and ORDERING and CONFUSION Tim Johnson <tim@celestialdesign.co.uk>
@ 2000-04-20 15:58 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Ross J. Reedstrom @ 2000-04-20 15:58 UTC (permalink / raw)
To: pgsql-sql
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..
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-04-20 15:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-20 01:28 Re: GROUPING and ORDERING and CONFUSION Tim Johnson <tim@celestialdesign.co.uk>
2000-04-20 09:21 ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
2000-04-20 15:58 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
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