Обсуждение: viewing the original (chrnological) order of entered records
Hi
I’ve the following problem:
I have a 2-column table with columns “person_id”(int4) and “phase”(text).
When I entered the following records in a chronological fashion: <1, “high school”>; <1, “childhood”> and <2, “university”>;
I requested the following select-statement.
SELECT person_id, phase FROM life ORDER BY person_id
And found the tuple <1, “childhood”> before the tuple <1, “high school”>.
I want to view the chronological order of my entries, but ordered by person_id.
Is this possible in postgresql?
No, at least not as you expect it. SQL returns the found records in
random order except for the explicit "order by" clause. So if you want a
chronological order, you have to supply some ordering fields to the
order by clause. This could be achieved easily by normalizing your
table, i.e. create a table like:
create table ages (
age_id smallint primary key,
sort_order smallint,
age_name varchar(100)
);
insert into ages values (1, 10, 'childhood');
insert into ages values (2, 20, 'high school');
insert into ages values (3, 30, 'univesrity');
NOTE: leave gaps in the sort order to accommodate for later insertions.
Then in the original table replace the names with age_id, and use a join
on the 2 tables, sorting by original_table.person_id, ages.sort_order.
HTH,
Csaba.
On Tue, 2003-06-10 at 11:50, Sven Van Acker wrote:
> Hi
>
>
>
> I've the following problem:
>
>
>
> I have a 2-column table with columns "person_id"(int4) and "phase"(text).
>
>
>
> When I entered the following records in a chronological fashion: <1, "high
> school">; <1, "childhood"> and <2, "university">;
>
>
>
>
>
> I requested the following select-statement.
>
>
>
> SELECT person_id, phase FROM life ORDER BY person_id
>
>
>
> And found the tuple <1, "childhood"> before the tuple <1, "high school">.
>
>
>
> I want to view the chronological order of my entries, but ordered by
> person_id.
>
> Is this possible in postgresql?
>
>
>
Solution #1:
Add a column to hold the time of entry:
ALTER TABLE life ADD COLUMN (entered_at timestamp); -- time of insert
ALTER TABLE life ALTER COLUMN entered_at DEFAULT now(); -- add it automagically
Then you can sort on this column, even if you don't select it for output (order by person_id, entered_at).
Disadvantage: Takes some extra space on disk. Use "WITHOUT OIDS" when creating the table to save some space.
Solution #2:
Use the OID of the row in the ORDER BY (order by person_id, oid). The OID is incremented for every row inserted.
Disadvantages:
This is unsafe, since it will fail when oid's wrap (after 2 billion inserts). That might not be a problem other than in theory :)
You cannot use "WITHOUT OIDS".
/Mattias
----- Original Message -----
From: Sven Van AckerSent: Tuesday, June 10, 2003 11:50 AMSubject: [GENERAL] viewing the original (chrnological) order of entered recordsHi
Ive the following problem:
I have a 2-column table with columns person_id(int4) and phase(text).
When I entered the following records in a chronological fashion: <1, high school>; <1, childhood> and <2, university>;
I requested the following select-statement.
SELECT person_id, phase FROM life ORDER BY person_id
And found the tuple <1, childhood> before the tuple <1, high school>.
I want to view the chronological order of my entries, but ordered by person_id.
Is this possible in postgresql?
On Tue, 2003-06-10 at 05:06, Csaba Nagy wrote: > No, at least not as you expect it. SQL returns the found records in > random order except for the explicit "order by" clause. So if you want a Following up on this: the reason it does not happen is because the relational algebra that underpins relations DBMSs acts on un-ordered sets. This is different from old pseudo-RDBMSs like dBASEIII in which you had to explicitly access rows by number. > chronological order, you have to supply some ordering fields to the > order by clause. This could be achieved easily by normalizing your > table, i.e. create a table like: > create table ages ( > age_id smallint primary key, > sort_order smallint, > age_name varchar(100) > ); > insert into ages values (1, 10, 'childhood'); > insert into ages values (2, 20, 'high school'); > insert into ages values (3, 30, 'univesrity'); > > NOTE: leave gaps in the sort order to accommodate for later insertions. > > Then in the original table replace the names with age_id, and use a join > on the 2 tables, sorting by original_table.person_id, ages.sort_order. > > HTH, > Csaba. > > > On Tue, 2003-06-10 at 11:50, Sven Van Acker wrote: > > Hi > > > > > > > > I've the following problem: > > > > > > > > I have a 2-column table with columns "person_id"(int4) and "phase"(text). > > > > > > > > When I entered the following records in a chronological fashion: <1, "high > > school">; <1, "childhood"> and <2, "university">; > > > > > > > > > > > > I requested the following select-statement. > > > > > > > > SELECT person_id, phase FROM life ORDER BY person_id > > > > > > > > And found the tuple <1, "childhood"> before the tuple <1, "high school">. > > > > > > > > I want to view the chronological order of my entries, but ordered by > > person_id. > > > > Is this possible in postgresql? > > -- +-----------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | Regarding war zones: "There's nothing sacrosanct about a | | hotel with a bunch of journalists in it." | | Marine Lt. Gen. Bernard E. Trainor (Retired) | +-----------------------------------------------------------+