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 -- [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly