question about a select
OK, I have the following table:
create table citations_by_level
(
aid smallint,
wid smallint,
v_level varchar(50),
w_level varchar(50),
x_level varchar(50),
y_level varchar(50),
z_level varchar(50),
byteloc integer
);
(If it helps, aid/wid identifies a text or work, the levels are
citation levels for that work (all but z_level potentially optional);
eg for some work y_level might indicate chapters [z_levels indicate
lines], and byteloc is the file position of that particular citation
in the work).
What I would *like* to be able to do is construct a query that groups
by a level, but sorts by byteloc. I don't seem to be able to do this.
Here are some examples. Note that y_level (any level) may have
duplicates (which I want to eliminate), and that it's ordering is
strictly on byteloc, not on its own value. postgres doesn't seem to
have envisioned this scenario and/or I'm not being creative enough in
constructing the query...
Text=# select distinct on (y_level) y_level from citations_by_level where aid=543 and wid=1;
y_level
---------
1
10
10a
11
12
13
14
15
16
17
18
19
2
20
21
22
23
24
25
26
27
28
29
2a
3
30
31
32
33
34
35
36
37
4
5
5a
6
7
7,8
8
9
t
(42 rows)
but as you can see, the "ordering" winds up being alphabetic on
y_level which simply does not do. [In this case it is only
coincidental that y_level appears numeric, it is a string and could be
anything; and the 7,8 is such an example]. The *byteloc* associated
with a given y_level (the location of that particular citation) does,
but I can't seem to use it:
Text=# select distinct on (y_level) y_level, byteloc from citations_by_level where aid=543 and wid=1 order by byteloc;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
Text=# select distinct on (y_level) y_level, byteloc from citations_by_level where aid=543 and wid=1 order by byteloc, y_level;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
???
--Cindy
--
ctmoore@uci.edu
On Thu, 9 Oct 2003, Cindy wrote:
Text=# select distinct on (y_level) y_level, byteloc from
citations_by_level where aid=543 and wid=1 order by byteloc;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
expressions
Maybe something like:
select y_level from (select distinct on (y_level) y_level,
byteloc from citations_by_level where aid=543 and wid=1) tab order by
byteloc;
Thanks for the speedy response!
Stephan Szabo writes:
On Thu, 9 Oct 2003, Cindy wrote:
Text=# select distinct on (y_level) y_level, byteloc from
citations_by_level where aid=543 and wid=1 order by byteloc;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
expressionsMaybe something like:
select y_level from (select distinct on (y_level) y_level,
byteloc from citations_by_level where aid=543 and wid=1) tab order by
byteloc;
Hm...this seems to pick out the y_level with the largest byteloc
associated with it...I need the y_level with the smallest byteloc and
then sorted by that...lemme play this, but any other suggestions also
welcomed...
(EG, I got:
Text=# select y_level, byteloc from (select distinct on (y_level)
y_level, byteloc from citations_by_level where aid=543::smallint and
wid=1::smallint) tab order by byteloc;
y_level | byteloc
---------+---------
13 | 24017
11 | 36231
8 | 47240
10 | 96777
2 | 285678
5 | 400212
7,8 | 420879
...
but if you look at the raw data (or some of it):
---------+---------
1 | 29
1 | 84
2 | 423
3 | 827
4 | 1005
5 | 1169
6 | 1530
1 | 1698
2 | 1988
3 | 2284
4 | 2460
5 | 2638
6 | 2924
7 | 3155
8 | 3396
1 | 3673
2 | 4095
3 | 4387
4 | 4699
5 | 4944
6 | 5055
7 | 5406
8 | 5704
9 | 5996
10 | 6349
1 | 6578
2 | 7110
I want
1 | 29
2 | 423
3 | 827
4 | 1005
5 | 1169
6 | 1530
7 | 3155
8 | 3396
9 | 5996
10 | 6349
etc...
--
Cindy
ctmoore@uci.edu
Import Notes
Reply to msg id not found: YourmessageofThu09Oct2003142918-0700.20031009142549.K73599@megazone.bigpanda.com | Resolved by subject fallback
On Thu, 9 Oct 2003, Cindy wrote:
Stephan Szabo writes:
On Thu, 9 Oct 2003, Cindy wrote:
Text=# select distinct on (y_level) y_level, byteloc from
citations_by_level where aid=543 and wid=1 order by byteloc;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
expressionsMaybe something like:
select y_level from (select distinct on (y_level) y_level,
byteloc from citations_by_level where aid=543 and wid=1) tab order by
byteloc;Hm...this seems to pick out the y_level with the largest byteloc
associated with it...I need the y_level with the smallest byteloc and
then sorted by that...lemme play this, but any other suggestions also
welcomed...
I think an order by y_level, byteloc in the subquery might give you the
ordering you want for the distinct on step, and then the outer order by
will order the y_levels by their respective bytelocs.